Table Design Question

Manat

Hello all,

I have a parts project, like bills of materials.
We have brands, and products within brands with many parts inside a product. All of these parts have to go into a database.

How should I structure the Database, we have one product that has 300 parts. Let's say in about 3 months, one brand has about 10,000 parts. Should I create tables on a per brand-basis?

The database can get huge. I'm just wondering how I should seperate everything. We have about 12 brands, let's say each brand has about 10 products(SKUs) within that brand.

Tables on a per-SKU basis is too crazy, there would be so many tables. And when a user searches for a part, it can take very long.

What are you suggestions?

Thanks
MK

drawmack

Three tables brands, products, parts - products links to brands and parts links to products. Pretty basic design

Manat

No sense to do that
My table has only 3 columns

skuNumber, partNum and partQty.

It should all be in only one table in my opinion because there are only 3 fields.

Also, partQty can vary depending on the SKU(Product).

So what would be the best way to do this, my table will eventually get HUGE.

Thanks, I apreciate any help from anyone.

Weedpacket

Every sense to do that: it's called "database normalisation". I mean, you said it yourself: "I'm just wondering how I should seperate everything".

drawmack

Database Design Rule 1: DO NOT REPEAT DATA IT LEADS TO CORRUPTION

Asking for help rule 1: When someone with more training and experience then you suggests something it's more well founded then your feeling about it.

TheDefender

Thank you for calling that out Drawmack. It's refreshing to see that kind of post. Manat, why ask for help if you are going to completely and blatantly disregard the help. I mean, it's one thing if you said something like "I will take it under advisement" and led drawmack to think you cared (no matter how slight) for his opinion... No matter what you did with the info later.

Otherwise, why even bother to come and ask in the first place? Humor your helpers manat. You just may learn something (no matter how much you already know, or think you already know).

Manat

Wow, talk about making a moutain out of a anthill.

Guys, I was merely expression my opinion, not giving final direction. Which is why i reposted.

If you look at my post you will see a note saying partQty can vary by SKU. You did not know this before since I had not mentioned it, so perhaps I thought that would influence your suggestion.

So if seperating it into 3 tables is your suggestion, i'm still unsure how that would work. Can you give an example of the table design of the 3 tables?
Because since there are only 3 fields, im wondering what will go into 3 different tables.

Thanks
MK

drawmack

Your initial post stated that you have brands, products and parts. Okay hypothetically let's say you're building computers. You're brands might be Gateway & Dell. Then inside of those you'll have products gw_comp_a, gw_comp_b, d_Comp_a and d_comp_b. Now each computer is going to have parts.

Here is the issue with repetative data. If you do not create a brands table then for each gateway computer you're going to have to reenter the name gateway. This could cause a problem as you could have GateWat, gateway, Gateway & gate-way in the database. They all mean the same thing but they are all different. You avoid this issue by creating a look up table named brands, and you do the same for computers. So you'd have something like this

Brands
brand_id -> auto_number
brand_name

Products
prod_id -> auto_number
prod_brand -> points to brand_id

Parts
part_id -> auto_number
part_prod -> points to prod_id
<what ever other fields you need here>

Then you have no data redundancy which is a good thing

Here are some resources on normalization for you:
http://www.databasejournal.com/sqletc/article.php/1428511
http://databases.about.com/library/weekly/aa080501a.htm
http://www.phpbuilder.com/columns/barry20000731.php3
http://support.microsoft.com/default.aspx?scid=kb;EN-US;100139

See how much more helpful I am when you're nice