CODEWITHSUNDEEP
database-design
chchrist
chchrist

Reputation: 19802

Database Design: Many-to-many question

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?

Upvotes: 1

Views: 826

Answers (3)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Hope this is self-explanatory.

product_color_size

Upvotes: 0

Filip Ekberg
Filip Ekberg

Reputation: 36287

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.

Upvotes: 1

Brian Hooper
Brian Hooper

Reputation: 22044

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.

Upvotes: 0

Related Questions