Forum Moderators: open
I want to have a database that allows me to list various widget brands, and if they offer certain colors. There can be 100 or more different colors as well as brands.
What would be the best way to setup a database table for this?
I have some ideas, but they all seen to not work.
thanks in advance!
one for the widgetbrand
one for the color
one for widgetproduct,desc,price
All depends on the length of the record(s) as to the effiency of the db. Most times, even with several thousands of records, a single table does it for me. Indexing and proper queries is the key to extracting the data.
What would be the best way to setup a database table for this?
Taking tangor's example, I would approach this a little differently. Put manufacturers/brands in a separate table as specified.
But for "color," imagine, if you will, a widget that may have hundreds of options, and that those options can change over time. Today it's just color; suppose you want to add an option for size? And the price of the item changes with the size? Tomorrow you have a widget that has variations of color, size and say, a trim?
You have a never-ending maintenance issue of adding more tables, modifying your code, and so on.
Consider an "options table" with the following attributes:
id product_id option_id option_name option_value required price
So at any time, you may add an option:
4321 1234 345435 color red 1 0.00
4322 1234 345435 color blue 1 0.00
4323 1234 865756 color green 1 0.00
4324 1234 345534 size small 1 7.95
4325 1234 344434 size medium 1 12.95
4326 1234 343334 size large 1 14.95
4327 1234 567567 trim silver 1 0.00
4328 1234 898778 trim gold 1 0.00
4329 1234 676967 widget cover 0 5.95
So for product ID 1234, you have the following options:
required:color
required:size, with differing prices
required:trim
optional:widget cover
This means, in your programming, one of the options of color, size, and trim MUST be selected to order the item.
Note that the option size has a price, and it is required. So what happens in this scenario is that the product price should be zero, but it doesn't really matter - your programming should override the base product price and use the option price for size to tally up the product subtotal.
Note also that "widget cover" has a price, but it is not required. If selected (as in, with a checkbox) it adds to the product subtotal, whether the price is in the "base product price" or not.
For shipping API calculations, extend the above to include item weight and dimensions.
This is an example and will work, to fully "normalize" the option names and id's should be in another table, joined on option ID. Your programming has to do careful input screening to make sure that two options with the same name (Color:red and color:red) don't get added to prevent the table from becoming unnecessarily bloated.
Using this approach will allow the site owner to add any option at any time, for any product, without you having to re-code or add database tables.
I was thinking of having a field in the db called "options" and in there put the IDs of the options, seperated by a comma:
12, 34, 2312, 13, 15, 15, etc
And put that field into an array to break out the options.
For a search, I'd do a like '%12,%" for every widget with that option
I'm afraid this won't work right, or the field would be too long for those with many options.
Any ideas?
Thanks!
So what if i have one widget that has 90% of the 100 available options. Whats the best way to reference this in the database?
In my scenario, depends on how you need to display it. Simply put,
select * from option_table where product_id=$prod_id order by [any order you like]
The problem with your comma separated idea is your field has to be text/varchar, making for slower lookups. Searches on integer fields are always faster. Also this
like '%12,%"
Would also capture
112,
212,
And would not capture the first or last item,
'12,...
..., 12
Plus it would complicate your programming, when you could do most of the heavy lifting in the initial select. Last, what about associating that option with prices, required, weights . . . ?
