Database Design Question

I'm fairly inexperienced when it comes to database design and programming bu
t
since our company is too cheap to hire anyone more experienced, I was hoping
someone here could help me out.
Our company assembles computers and we want to develop an order processing
system, so here is the set up.
Each order can have many computers.
Each computer is made up of many components (CPU, Motherboard, RAM, etc.)
Each component has many properties (Brand Name, Speed, Capacity, etc.)
So I want to know the best way of setting up my tables.
Just looking at the computer table, the first way I considered was for each
computer would take up one row and all its components would take up the
columns. For Example:
SerialNumber - CPU - Motherboard - Memory - Harddrive - CDRW - etc.,
111111 - P4 - ASUS - 512 - WD80 - LG CDRW - etc.,
222222 - Athlon 64 - Gigabyte - 1GB - Maxtor 80GB - BenQ DVD RW - etc.,
You can see the problem. I have no idea how many columns I'll need because a
computer can have only a few components or dozens including multiple drives,
graphics cards, memory sticks, and so on.
The second way I considered was instead of having everything on a horizontal
line, to make it vertical. So my columns would be SerialNumber,
ComponentType, ComponentName. And that would look like this.
SerialNumber - ComponentType - ComponentName
111111 - CPU - P4
111111 - MotherBoard - ASUS
111111 - Memory - Kingston 512MB
... etc...
222222 - CPU - Athlon 64
222222 - MotherBoard - Gigabyte
222222 - Memory - Corsair 1GB
... etc...
That's certainly much more flexible in terms of adding components to a
computer, but I'm worried about how big the table might get. For example, if
we get orders for 500,000 computers, each with 10 - 15 components, that's
over 5 million rows. I've never dealt with large databases and tables before
so is that something I should worry about or can SQL Server handle that
easily?
Or is there a different design I should consider? Any direction or links
would be appreciated.
Thanks.> Our company assembles computers and we want to develop an order processing
> system
Why? You can buy one off the shelf for much less than it will cost to
develop, especially given your inexperience.
For your info SQL Server is capable of handling tables with 100s of millions
of rows. On the other hand a poor design can fail whatever the data size.
Your second design makes far more sense but it's obvious you have a lot to
learn. I recommend you give up on developing the order processing system and
invest the time on a course instead.
David Portas
SQL Server MVP
--|||It is not clear from your question how you plan to use the information. Do
you want to track inventory? Lot/Serial information? Cost? Labor? What
documents do you need to produce? Inventory Picking sheets? Routings? Bills
of lading? Purchase Orders? Invoices?
It would probably be better to pick something off the shelf and customize it
than to roll your own.
"pete K" <peteK@.discussions.microsoft.com> wrote in message
news:9A529093-75C9-4E66-BD5B-9A0EFCCDF70E@.microsoft.com...
> I'm fairly inexperienced when it comes to database design and programming
but
> since our company is too cheap to hire anyone more experienced, I was
hoping
> someone here could help me out.
> Our company assembles computers and we want to develop an order processing
> system, so here is the set up.
> Each order can have many computers.
> Each computer is made up of many components (CPU, Motherboard, RAM, etc.)
> Each component has many properties (Brand Name, Speed, Capacity, etc.)
> So I want to know the best way of setting up my tables.
> Just looking at the computer table, the first way I considered was for
each
> computer would take up one row and all its components would take up the
> columns. For Example:
> SerialNumber - CPU - Motherboard - Memory - Harddrive - CDRW - etc.,
> 111111 - P4 - ASUS - 512 - WD80 - LG CDRW - etc.,
> 222222 - Athlon 64 - Gigabyte - 1GB - Maxtor 80GB - BenQ DVD RW - etc.,
> You can see the problem. I have no idea how many columns I'll need because
a
> computer can have only a few components or dozens including multiple
drives,
> graphics cards, memory sticks, and so on.
> The second way I considered was instead of having everything on a
horizontal
> line, to make it vertical. So my columns would be SerialNumber,
> ComponentType, ComponentName. And that would look like this.
> SerialNumber - ComponentType - ComponentName
> 111111 - CPU - P4
> 111111 - MotherBoard - ASUS
> 111111 - Memory - Kingston 512MB
> ... etc...
> 222222 - CPU - Athlon 64
> 222222 - MotherBoard - Gigabyte
> 222222 - Memory - Corsair 1GB
> ... etc...
> That's certainly much more flexible in terms of adding components to a
> computer, but I'm worried about how big the table might get. For example,
if
> we get orders for 500,000 computers, each with 10 - 15 components, that's
> over 5 million rows. I've never dealt with large databases and tables
before
> so is that something I should worry about or can SQL Server handle that
> easily?
> Or is there a different design I should consider? Any direction or links
> would be appreciated.
> Thanks.|||Personally, I agree with you and those were the same concerns I expressed to
the president of my company. But he's the type of person who, once he gets a
n
idea in his head, he can't be shaken of it, and this is one of them. So what
can I do but humour him? It's his company and if he wants to waste the time
and money on a project that is likely beyond my skill set despite my
protestations to the obviously, well, all I can do is go along with it and d
o
my best. But hey, that's life I guess.
"David Portas" wrote:

> Why? You can buy one off the shelf for much less than it will cost to
> develop, especially given your inexperience.
> For your info SQL Server is capable of handling tables with 100s of millio
ns
> of rows. On the other hand a poor design can fail whatever the data size.
> Your second design makes far more sense but it's obvious you have a lot to
> learn. I recommend you give up on developing the order processing system a
nd
> invest the time on a course instead.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You should start with Database Design for Mere Mortals by Michael J.
Hernandez. Even with that start, you'll still screw things up and have
plenty of questions here, but it's a pretty good baseline to understanding
where you are headed.
I'm going to set the over/under line on this project at about 1 1/2 years or
$80,000, whichever comes first.
"pete K" <peteK@.discussions.microsoft.com> wrote in message
news:9A529093-75C9-4E66-BD5B-9A0EFCCDF70E@.microsoft.com...
> I'm fairly inexperienced when it comes to database design and programming
> but
> since our company is too cheap to hire anyone more experienced, I was
> hoping
> someone here could help me out.
> Our company assembles computers and we want to develop an order processing
> system, so here is the set up.
> Each order can have many computers.
> Each computer is made up of many components (CPU, Motherboard, RAM, etc.)
> Each component has many properties (Brand Name, Speed, Capacity, etc.)
> So I want to know the best way of setting up my tables.
> Just looking at the computer table, the first way I considered was for
> each
> computer would take up one row and all its components would take up the
> columns. For Example:
> SerialNumber - CPU - Motherboard - Memory - Harddrive - CDRW - etc.,
> 111111 - P4 - ASUS - 512 - WD80 - LG CDRW - etc.,
> 222222 - Athlon 64 - Gigabyte - 1GB - Maxtor 80GB - BenQ DVD RW - etc.,
> You can see the problem. I have no idea how many columns I'll need because
> a
> computer can have only a few components or dozens including multiple
> drives,
> graphics cards, memory sticks, and so on.
> The second way I considered was instead of having everything on a
> horizontal
> line, to make it vertical. So my columns would be SerialNumber,
> ComponentType, ComponentName. And that would look like this.
> SerialNumber - ComponentType - ComponentName
> 111111 - CPU - P4
> 111111 - MotherBoard - ASUS
> 111111 - Memory - Kingston 512MB
> ... etc...
> 222222 - CPU - Athlon 64
> 222222 - MotherBoard - Gigabyte
> 222222 - Memory - Corsair 1GB
> ... etc...
> That's certainly much more flexible in terms of adding components to a
> computer, but I'm worried about how big the table might get. For example,
> if
> we get orders for 500,000 computers, each with 10 - 15 components, that's
> over 5 million rows. I've never dealt with large databases and tables
> before
> so is that something I should worry about or can SQL Server handle that
> easily?
> Or is there a different design I should consider? Any direction or links
> would be appreciated.
> Thanks.

Database Design

Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible. The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max
Max,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible. The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max

Database Design

Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max/*
Here is a quick, down and dirty design (SQL Server or MSDE only, as it is
T-SQL). Copy and paste to Query Analyzer and take a look:
NOTE: Commented out so you can copy the entire post and run it in Query
Analyzer.
*/
CREATE TABLE [dbo].[Author] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuthorPublicationAssoc] (
[AuthorPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Keyword] (
[KeywordID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[KeywordPublicationAssoc] (
[KeywordPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Place] (
[PlaceID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Publication] (
[PublicationID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceID] [int] NULL ,
[PublicationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PublicationYear] [smallint] NOT NULL ,
[NumberOfSites] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Author] WITH NOCHECK ADD
CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_AuthorPublicationAssoc] PRIMARY KEY CLUSTERED
(
[AuthorPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Keyword] WITH NOCHECK ADD
CONSTRAINT [PK_Keyword] PRIMARY KEY CLUSTERED
(
[KeywordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_KeywordPublicationAssoc] PRIMARY KEY CLUSTERED
(
[KeywordPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Place] WITH NOCHECK ADD
CONSTRAINT [PK_Place] PRIMARY KEY CLUSTERED
(
[PlaceID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] WITH NOCHECK ADD
CONSTRAINT [PK_Publication] PRIMARY KEY CLUSTERED
(
[PublicationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [DF_Publication_NumberOfSites] DEFAULT (0) FOR [NumberOfSites]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] ADD
CONSTRAINT [FK_AuthorPublicationAssoc_Author] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Author] (
[AuthorID]
),
CONSTRAINT [FK_AuthorPublicationAssoc_Publication] FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] ADD
CONSTRAINT [FK_KeywordPublicationAssoc_Keyword] FOREIGN KEY
(
[KeywordID]
) REFERENCES [dbo].[Keyword] (
[KeywordID]
),
CONSTRAINT [FK_KeywordPublicationAssoc_Publication]
FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [FK_Publication_Place] FOREIGN KEY
(
[PlaceID]
) REFERENCES [dbo].[Place] (
[PlaceID]
)
GO
/*
This is just basic layout, so alter to your hearts content.
As for database, that really depends on the app and the amount of traffic. I
like SQL Server (and assume you do to since you are posting in a SQL Server
group), but it is not the only database out there.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"max" wrote:

> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
*/

Database Design

Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible. The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible. The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max

Database Design

Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max