Database design that I can’t seem to wrap my head around.
Adding MySQL/PHP on a 10 year old html/css/js site. Current avg page size of about 75k.
I Have:
1100 transfers //paint by number patterns
another
700 transfers catagorized by a sku#
120 different paints // each transfer recommends up to 28 paints
I need to end up with a detail page of a transfer that includes an (html)table
with the up to 28 recommended colors. Each color (png background) includes
checkbox for inclusion with the transfer order.
Any comments welcome something will turn the light on…
Paints
_id
name
number
price
discription
image1
image2
Transfers
_id
name
number
price
discription
image1
image2
Quilt sets // 6 to 12 blocks each… but each block available also
_id
name
number
price
discription
image1
image2
In the column to point to paints needed I thought of using an array of color numbers (p101, p102, p103) etc.
But in Kevin Yanks phpmysql5 book he states:
“Once again, many inexperienced developers begin to think of ways to store several
values in a single column, because the obvious solution is to add a category column
to the joke table and use it to list the IDs of those categories to which each joke
belongs. A second rule of thumb would be useful here: if you need to store multiple
values in a single field, your design is probably flawed.”
Do I need 28 columns for links what if she comes up with something with more colors, or all paints for a quilt of 12 blocks.
as I see the join tables are only dealing with a couple items only and it appears to be just one to one vs linking up 28/120 possible paints to 1800 patterns calling on 28/120 paints.
Multiple values in one column is a no-no. But so is the paint1, paint2, paint3 … paint28 column solution. These are clear indications that your database design has to be normalized.
What is the relation between the tables?
1 paint has 1 to n transfers? 1 transfer has 1 to n paints?
Idem for the paint and quilt sets tables, and the transfer and quilt set tables.
Once you’ve defined the relation between the tables, you then can decide if you need some joining tables.
Transfers
_id
name
number
price
discription
image1
image2
Paints
_id
name
number
price
discription
image1
image2
1 transfer can have up to 28 paints, right? And surely a paint can be used in more than 1 transfer.
So you need a transpnt table (as you said), which will have only 2 columns:
transfer_id
paint_id
In this table you’ll save a row for each transfer-paint combination that is inserted by the admin.
Attention: the way you store data in your database, and the way you display it to the user, or the way you let the user insert it, isn’t always 1-on-1.
In this case, if you want to limit the max number of paints for 1 transfer to 28, you’ll have to impose that limit in your PHP code (or whatever language you use). And if you want to have the user insert the paints like this:
value 1, value 2, value 3, ...
that’s ok too. Again, in your PHP code you’ll have to sanitize and validate any user input anyway, and you can also turn a comma separated value string like that in single values that you’ll insert in the table.
