Forum Moderators: open
I need to store varied information about several types of item and their location within a warehouse.
Within a warehouse I might have 16 crates of hammers, 8 crates of nails and 4 crates of bricks, all in a line (order).
I have an item_warehouse table which links the item_id to the warehouse_id.
The problem I am having is that new items will be added all the time and would each need their own table as they will all need different information storing. Hammers for example might need weight, while nails will need length.
What is the best way for me to store multiple item types and values in a futureproof way with minimal tables?
So far I have:
A table which links warehouse to item and stores the order in which the items are lined up.
A table which links an item_type_id to the name of the item type.
Now I could do one of the following:
1) Have a table of item value names and a table of actual values.
2) Have a table: item_id ¦ name ¦ value
3) Add a new table each time I add a new item type.
Does anyone have a good method for dealing with this? Or a good tutorial about such a case?
Thank you.
Hammers for example might need weight, while nails will need length.
Here is how I "micromanage" these situations.
Don't think in terms of specific values. One such approach was to make a list of all possible values, but unless you're omnipresent, the one value you need won't be there. :-) Think in terms of generic options.
Start with your base product table. So Bill, why is price and weight in the product table and set to 0? Generally you will always need a weight for shipping calculations, and if there are no options for this item, you need only the base product table.
products
auto_id ¦ prod_id ¦ title ¦ price ¦ weight ¦ description
1 ¦ 1234 ¦ 12 oz. Hammer ¦ 0.00 ¦ 0.00 ¦ Carpentry hammers
2 ¦1235 ¦ 16p Nails, 500 ct ¦ 0.00 ¦ 0.00 ¦ 500 to a box
3 ¦1236 ¦ Leather Work Belt ¦ 75.00 ¦ 2.25 ¦ Hammer strap, tools, nail pouch
Create a table for product options that contains fields for option_name and option_value.
options
auto_id ¦ prod_id ¦ option_name ¦ option_value ¦ price ¦ weight
1 ¦ 1234 ¦ Size ¦ 12 oz. ¦ 12.00 ¦ 0.75
2 ¦ 1234 ¦ Size ¦ 16 oz. ¦ 14.00 ¦ 1.00
3 ¦ 1234 ¦ Size ¦ 18 oz. ¦ 16.00 ¦ 1.25
4 ¦ 1234 ¦ Handle ¦ Wood ¦ 0.00 ¦ 0.00
5 ¦ 1234 ¦ Handle ¦ Fiberglass ¦ 0.00 ¦ 0.00
6 ¦ 1235 ¦ Length ¦ 3 in. ¦ 32.00 ¦ 8.75
7 ¦ 1235 ¦ Length ¦ 5 in. ¦ 36.00 ¦ 10.75
8 ¦ 1235 ¦ Length ¦ 9 in. ¦ 48.00 ¦ 22.75
9 ¦ 1235 ¦ Type ¦ Iron ¦ 0.00 ¦ 0.00
10 ¦ 1235 ¦ Type ¦ Aluminum ¦ 0.00 ¦ 0.00
11 ¦ 1235 ¦ Type ¦ Titanium ¦ 0.00 ¦ 0.0
Some caveats:
- The first thing you might notice is this approach is not fully normalized, as there are multiple rows with the same values (option name.) A fully efficient solution would break this up into even more tables, but get your head around this first before attempting.
- In reality Iron, Aluminum, and Titanium nails would be different prices and would be a price calculated against the box count, so using this structure you would be better off with three products for each of those. Again, example only.
- Corollary to the above, your programming would have to make sure you always enter the same value for an option, that is, Size and size would create two different options. This also goes away if you split the option names off to another table.
- Note how both items have two options, and each option can have an infinite number of values. At any time, you can add or delete options, as many as you want or need, for these items and they can be any value you need, so long as only ONE of those options (or none) affects the price. If the item has no options, you use the weight and price field. If it has options, in your programming you use these fields; if not, you use the option values:
$price = ($option_price > 0)?$option_price:$base_price;
Ditto for weight, or any other variable options.
- This is not a "complete" scenario. I usually also have fields for required (if an option is required for selection,) active (so you can inactivate an item option without physically deleting it,) and dimensions (for shipping calculations.)
So yes, more complex queries are required but you will have a more robust expandable database from which to work.
Where $prod = 1234 (hammers,)
select auto_id, option_name, option_value, price from options where prod_id=$prod group by option_name;
Should give you something like
1 ¦ Size ¦ 12 oz. ¦ 12.00
2 ¦ Size ¦ 16 oz. ¦ 14.00
3 ¦ Size ¦ 18 oz. ¦ 16.00
A single query can be done with joins to get a line with the full information for the product, including data from the products table.
I have one further question / requirement.
The product options will all need searchable descriptions adding to them. I wouldn't want to put them in the same table as the short name value pairs
Another table?
In my "scenario," the description field is in products, and any relevant differences in the options can be explained there. (Not that anyone reads these days!)
Lets say that one of the possible options, along with size / length / handle is marketing speel or further instructions.
Because it is text I want to seperate it and allow it to be searchable
The solution I came up with is two tables which can be unioned in a query.
one is values -> item_id ¦ option_name ¦ option_value
one is content -> item_id ¦ option_name ¦ option_text
Can you see any issues with this? When creating a page it is just a union of the two then some php to sort. When it is an insert it just looks are rules as to where to put it.
