Design of My Tables

[zyman10]

I am trying to make sure I design my database correctly/optimized and easy to understand.

So what I did, was make 3 tables.

Parts:
PartNum
Brand
Description

Products:
ProdNum
Title
KitNum
PartNum

Kits:
KitNum
PartNum
Description

So I am selling this stuff in my store and I only want 1 number tracking each sale. So I decided I was only going to sell products. A part is a single item. It can be sold by itself as a product (with a unique prodNum), or several parts make up a Kit, which is sold as a product (with a unique prodNum).

So my Products Table either excepts KitNum or PartNum. If it is a partNum, then I enter NULL for KitNum, and visa versa. If its a part I am simply selling that part in my storefront, using the description from the Parts Table. Done.

If its a kitNum then it gets a little complicated. What I did was, I repeat that KitNum over and over in the Kit Table with however many parts are involved in that kit.

I sell Product12 in my store, and it is actually Kit3. Kit3 contains Part4, Part5, Part6 and Part7.

Kit Table would look like
KitNum --- PartNum --- Desc
kit3 --- Part4 --- Description1
kit3 --- Part5 --- Description1
kit3 --- Part6 --- Description1
kit3 --- Part7 --- Description1

Product Table would look like
ProdNum --- PartNum --- KitNum
Product12 --- NULL --- kit3

Is this good design? I've been back and forth on this, and I just can't figure out a good way to have multiple parts in a product because the number of parts could go out to like 20... so i don't want 19 extra fields in the Products table to be wasted every time i sell a part as a product... i dunno I'm kinda stuck here. There's gotta be a better way to do this... but maybe not?

Thanks in advance.

 

[zyman10]

Ohh... I have no problem changing how I think about this if it is going to make things simpler in the long run.

Thanks again.

 

[hmckillop]

You effectively have two FK keys on the product table when you only need one.

Think of it this way, a product consists of one or more parts eg. which in your terms is a kit.

So only have product reference the kit table and always populate kit with a record regardless if it is made up of one item or more.

e.g.

Parts:
PartNum
Brand
Description

Products:
ProdNum
Title
KitNum
--PartNum (NOT REQUIRED ANY MORE)

Kits:
KitNum
PartNum
Description



This means you have a consistent way of accessing the data that doesnt change per product. Your application can then present this however it wants based on the returned values ..
Just my suggestion

"I'm living so far beyond my income that we may almost be said to be living apart

 

[zyman10]

Ok I think I changed how I am going to do it.

Parts:
PartNum
Brand
Desc

Products:
ProdNum
Title
Desc

ProductMapping:
ProdNum
PartNum
Quantity

So this way i'll just have parts and products, and then I don't have to repeat the Description over and over and then I have the whole mapping of multiple Parts to 1 Product taken care of. Thanks for the help hmck, you helped me realized what I wanted to do after being stumped for the longest!