rocknbil - 5:52 pm on Feb 2, 2011 (gmt 0)
Right, if it's not too late, you'd be better off at least starting with something like this:
1|1234|Dog Collar|Some description|...
2|4567|Choke Chain|Another description|....
3|6565|Dog Bed|Some other description|...
Even this is not fully normalized, note the multiple occurrences of Color and Size. Take it one step further in four tables:
You tie those to products with the final table - the parentheses are just for notes here, not actual table data:
1|1234|1 (Collar Size 10cm)
2|1234|2 (Collar Size 20cm)
3|1234|3 (Collar Size 30cm)
4|1234|4 (Collar Size 40cm)
5|4567|5 (Choke Chain Size 50cm)
6|1234|6 (Collar Color Black)
7|6565|2 (Dog Bed Size 20cm)
8|6565|3 (Dog Bed Size 30cm)
9|6565|6 (Dog Bed Color Black)
10|4567|7 (Choke Chain Color Silver)
11|4567|8 (Choke Chain Color Gold)
Then you do your selections by a combination of group by and left joins. This my **seem* like a huge undertaking, but when you get through it, look at the advantages:
- Normalization - you optimize your tables by making them lean and mean, eliminating row after row of the same data, which will slow down all of your queries.
- selecting by integer data - when you do your joins, they will be on integer fields as you can see, and those will **always** be faster than textual selections.
- Simplifies your code - instead of figuring out complex textual pattern matches as you're attempting now, the data will be reliable and absolute, and will not change over time (what happens if you use "colour" instead of "color?")
- You can change the names of your options or even the values at any time without having to reprogram anything or mess with database entries. Using my example, this changes absolutely nothing in your database, everything will still work:
- Selecting and ordering becomes a breeze, you can now order by any of the textual values you want, depending on the situation. For example, when outputting a drop down list for say, the collar sizes, you can order it by option_value.value asc, which will go from 10cm up. No futzing around using up memory by stuffing values in an array and using programming sorting.
- Calculations - though it's not exemplified here, for a merchant, the potential will exist - and you will likely encounter - the need for price variants, for example, prices will vary for small, medium, and large. You can add a price field to your product options table. Leave it at zero for the base price of the product, but enter values and let these override the base price if present. An additional complication, what if it's an ADD ON?
Dog collar carrying case
You can add a field, call it "required" that defaults to 1. If it's a zero, this means you use the base price and if any priced options are selected they add to the price.
(In truth, for full normalization price info should likely be saved in another table, especially if only a few records will contain price variants.)
- And the biggest, baddest one of all, from the standpoint of a merchant: you will never have to modify your code to expand or apply this to virtually any product. As you need a new option, you add it:
This makes it expandable, meaning you can have a near infinite combination of option names and associated values - AND - look closely at the dog collar and dog bed colors. Note how they are distinctly different items but they are using the very same entries for "color." This is called normalization.
It's a hard concept to get your head around, the selects will drive you nuts at first, but it's well worth it.