I have a database full of products which obviously have their own product ids
There comes a time now when some of my products are the same thing but in different sizes (i.e. T-Shirt S, M, L and XL). They would all have the same name but with a different product id, but how would i group them for output.
On the site where it’s going to appear, i would like a select box with the different sizes for the product (if there are different sizes).
I would imagine each product would have it’s own product id and in another field there would be a sub id. This could be left blank if there are no other sizes.
The correct way of doing this is to create another table where you have rows that represent a size of a product.
Something like
product
-----------
id
name
color
(etc)
productsize
----------------
productId
size
And then just add the sizes for a product to the productsize table one by one. When you show a product just JOIN it with the productsizes (or do a separate query, up to you) and show the dropdown if there are multiple sizes.
An alternative would be to store all sizes in a comma separated field in your products table, so you get a field with a value like “S,M,L,XL”. You can split the string on commas and get an array of sizes.
The drawback of this method is that becomes harder (or even impossible) to search in the field; i.e. if someone wants to see all blue medium t-shirts that would be harder.
It’s also not possible to keep extra information with the sizes (like stock, “we have 4 medium, 3 large, etc on stock”).
However, if such searches will not be needed and you don’t need to add extra information, but you’re just using it for lookup, this way (though incorrect) is heck of a lot easier.
you haven’t shown how that would be done in your one-to-many table, either
besides qty_on_hand, something else that may vary by size is the price
now, if you try the same approach, and have a separate one-to-many table for prices, with productid and price as the columns, you immediately run into a problem – how to match the correct price to each size
follow this line of reasoning far enough, and you will eventually discover another approach altogether, and that is not to have a single product row and then mangle the sizes and prices and other variable stuff in some way, but simply to have everything in the products table
thus, if the t-shirt comes in S, M, L, and XL, then there will be 4 rows in the product table
which is what adrock said… “They would all have the same name but with a different product id”
which makes me wonder if this might be the way forward?
create table products
( id int not null auto_increment primary key
, product_number varchar(24) NULL
, product_name varchar (99) not null
) engine=innodb etc
create table product_prices
( id int not null
, product_id int not null
, price decimal(6,2)
, currency_code char(3)
) engine=innodb etc
create table product_attributes
( id int not null
, product_id int not null
, attribute_name varchar(99) eg colour/size/weight
, attribute_value varchar(24) not null eg red/44/250
, attribute_unit_of_measurement varchar(6) NULL eg kg/quart/gall/ml
) engine=innodb etc
Though I can’t work out for you, how to make the structure work if the price may (in some cases), be specific to the size (for example) and in others be standard for all variations of attributes.
hth somehow.
search a suggestion oddz made a few weeks back, on which this suggestion is based.
I assumed that would be trivial, but okay. Here it is:
product
-----------
id
name
color
(etc)
productsizes
------------
productId
size
stock
Make (productId, size) the primary key of productsizes and then use that tuple to identify product and size ordered.
Which is prone to update anomalies, my suggestion is not.
@IBazz what you’re suggestion is known as Entity-Attribute-Value (EAV) and should IMHO be avoided like the plague – it’s only to be used if all else fails.
I don’t think all else has failed in this thread just yet
Not necessarily. You could also set a price in the product itself that is used when there are no sizes.
What I gather from your posts is that you’re proposing something like this
id name size description
---------------------------------------------------------------
1 T-shirt 1 S This is a ncie t-shirt that .... (etc)
2 T-shirt 1 M This is a ncie t-shirt that .... (etc)
3 T-shirt 1 L This is a ncie t-shirt that .... (etc)
4 T-shirt 1 XL This is a ncie t-shirt that .... (etc)
And than you would sort of “group by” (not in the SQL sense) name so that T-shirt 1 only shows once on the product listing, right?
As you can see I’ve made a typo in there (completely by accident (:): “ncie” should be “nice”. Using this scheme I should update all 4 rows myself? Or I should let the application handle it, which is also not very nice IMO (it could crash half way …). Plus I’d have to store the description multiple times (redundancy).
(Okay, update anomaly was not the correct term here. Sorry for that one.)
have you ever seen any of my posts with regard to firstname?
suppose you have a customer or employee or whatever persons table, in which you store the firstname and lastname of people
a lot of people are named John, but does this mean you have to split off the first name into its own “firstnames” table, to avoid the redundancy?
three answers:
no, that’d be silly
redundancy isn’t always bad
even if you replace the firstname with a lookup table, you haven’t eliminated the redundancy, because the same number of (redundant) FKs will be pointing to John in the firstnames table
The Johns actually all have different names (even though they are the same) in that if one John decides he doesn’t want to be called John anymore and changes his name to Bob, I should only change that John to Bob, not all Johns.
In the case of the product description, if I want to change that, I need to change it in all rows that pertain that specific product (unless you’d want a completely different product description for each size, which seems unlikely).
