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

I m designing a data base with SQL Server 2000, and I m an inexperienced with this . I would like to know whether the structure that I have now is efficient, and if I could improve it . The database is for a website that allows you to insert an offer of a car of second hand. Here is a piece of structure of offers database:

Table 1:
Offer_Num (Key Field)
Offer_Date

Table 2:
Kind_of_car_Num
Trade_Name_Num
Model_Num
Offer_Num (Key Field)

Table 2-a:
Kind_of_car_Num (Key Field)
Name_of_Kind_of_car

Table 2-b:
Trade_Name_Num (Key Field)
Trade_Name

Table 2-c:
Model_Num (Key Field)
Model_Name

Table 3:
Car_Price
Offer_Num (Key Field)

Table 4:
Car_Colour
Offer_Num (Key Field)

Tabla 5:
Matriculation_Car
Offer_Num (Key Field)

The tables 1, 2, 3, 4 and 5 there are linked throughout the key field Offer_num like a sequence (I linked table 1 with table 2, table 2 with 3, table 3 with 4 and table 4 with 5). The type of data of the Offer_num are Identity in all tables, and table 2 are linked with table 2-a, 2-b and 2-c.

When it makes the data base of clients, I must connect all the tables by the key field "Number _of _client"? like in this example?

ThanksNot knowing the meaning of some of the data, this could be a bit dangerous for me to comment on. Let me just put on my asbestos suit, and take an educated guess or two...

Normally, the rule of thumb that I use is to figure out what "objects" I am trying to model in the database. In this case, it appears to be Offers, Cars, and possibly Clients. I generally try to make a table for each kind of object I am modelling, so I would have a table of Cars, and a table of Offers.

The objects can have several "Attributes" (color of a car, amount of an offer, etc.). Some or all of these can be made into tables of thier own. There is not really a hard and fast rule for this, so this is where the holy wars begin. If you have very few values for your attributes for a large population of objects, (and especially if you want to report on all "red" cars), then I would go ahead and give those attributes their own tables. These attributes are then mapped to cars by ids in the Cars table. This approach gives you much more control over the attribute values that are entered, which is important for some reporting. Also, if you are giving folks the ability to search this database, you want to give them a nice set base of values to look for. I would dread searching for a car in the local newspaper in a programatic way. How many ways are there to spell "Volkswagon" when you are paying by the word, anyway? But I digress.

If you have some attributes that are absolutely inseparable from an object (such as offer amount), then I would put those attributes on the Offers table as columns.

Now the fun really begins, because you can have many Offers on a single Car, with just two tables representing Cars and Offers. This is where you have to use a Mapping table (Car_ID, Offer_ID) to resolve all offers on a car. If you look in the pubs database, you can see this in the titleauthor table in the pubs database, where many titles can be produced by one author. Coincidentally, several authors can contribute on one title, and this construct still comes out fairly clean.

All of this does not really help the original question, but hopefully this will give you a little insight into the workings of database design.

Now for the disclaimers: All of these points raised are my own not-so-humble backward opinion, and should be taken as such rather than the gospel truth some folks may assume it is. There are many ways to skin a cat, but the cat skin market has pretty much bottomed out, so don't bother the cat.|||Thanks for your explanation of as you design a data base and the main elements that takings in consideration. Yes, all of this does really help the original question, and it clarify many doubts to me that nonwise nor like requesting them, thank you very much Mcrowley.

Csar