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.