Hi all,
I have a database with these tables:
products,colors,sizes
Each product(t-shirts,jeans etc) can have multiple colors and each color can have multiple sizes
EDIT
Products can share colors and colors can share sizes
So I've made an xref_products_colors table and xref_color_sizes table. Is that OK or there is a better way to design this database?
First off, modelling an N-to-N relationship as a separate link table is fine.
What strikes me as odd in your particular model, however, is that sizes are linked to colours. This seems wrong, as color has inherently nothing to do with size.
I personally would probably put both color and size in the same table to express that there are only particular combinations of these two properties, and then link this table to the products table.
Isn't each pair of jeans with different colors their own product? A product is "unique" in its collection of sizes and colors.
I would do something like this:
Collection or Brand might even have both where a Collection aggregates a Brand.
Then I would have this layout in products
Id, Size, Color, Collection
However, you would see mulitple lines of Size, Color in this scenario so you could even see a different Color as a Different Collection whereas the Collection table could look like this:
Id, Brand, Color
And then you attach the Products with a specific size to the Collection like this:
Id, Collection, Size having it a bit more normalized.
By the looks of it you need to either adopt Mr Niedermair's suggestion or have three tables, PRODUCT with a product_id key, COLOURS with a product_id and colour_id key, and SIZES with a product_id, colour_id and size_id key.
It isn't really an N-to-N relationship although it looks like it, because someone who wants a red shirt isn't likely to be interested in a red car as an alternative.
Edit - the answer I referred to has just disappeared. In it, the responder suggested having a table PRODUCT keyed on product, colour and size.
