Hey folks. I am trying to setup a table for a client and having a couple of issues trying to design it. I have a list of 12 different species of wood. 16 different colors of Paint. 19 Different stains, and and basically 10 different Glazes. Only certain paints and stains can be applied to each specie of wood. Only certain glazes can be applied with either Paint or stain. So for instance - Cherry wood can get Apline stain as the base stain(color) and the glaze options are either White or No Glaze. I would like to do this in one table if I can. Any ideas?
You aren’t going to properly handle it in a single table. Have you attempted an ERD?
In a properly normalized database you are going to need no less than 5 tables.
tbl_woods
tbl_paint_colors
tbl_stains
tbl_glazes
tbl_joiner
You may be able to consolidate the paints, glazes, and stains to a single table with a type enum or type id to simplify things.
The compatibility issue would take some work, however.
Enum? 45 Enum items? Simplify? :o
I will give you a few minutes to think about that.
Kevin - eumm - yeah - thinking - ;D
tbl_joiner will be like 1,2,2,1 (PKs from other tables)
JM
Given: You can only have a stain or paint, can’t have both.
Table Stucture
tbl_woods
1,Alder
2,Cherry
tbl_paint_colors
1,Autumn
2,Fall
tbl_stains
1,Brown
2,White
tbl_glazes
1,Coffee
2,Chai
tbl_joiner
??
No, you would enumerate on the type. Stain, Glaze, Paint. 45? I see 3.
You could then have a compatibility table to the id of the top coat.
Given: You can only have a stain or paint, can’t have both.
Table Stucture
tbl_woods
1,Alder
2,Cherry
tbl_paint_colors
1,Autumn
2,Fall
tbl_stains
1,Brown
2,White
tbl_glazes
1,Coffee
2,Chai
tbl_joiner
??
FYI Fellow experts: There is much, much more involved than just what the OP has posted. Spending anytime coming up with a “solution” to the posted question will not be helping the OP and will not be time well spent on your part. I am handling this offline with the OP.