1

I have a Product and a Product Category.

I can define a Product has Product Category relationship, or Product Category has Product.

Both make sense to me. My aim is to achieve a grouping of products to categorize them more easily. I see Product Category as a helper concept and not a concrete concept. i.e. a Product is something I could sell, something I can place into a shopping cart, etc. I cannot do that with a Product Category. But I can use it to group things, i.e. show a listing of Productthat belongs to a certain Product Category.

Many to Many relation is symmetrical, I assume, and there are two ways to create it. How can I determine which direction is correct in my case? Is there one?

Dennis
  • 7,907
  • 11
  • 65
  • 115
  • What do you mean, two ways to create it? Or, directon? Maybe, which column is 1st in a list? Who cares? [Are There Bidirectional Relational Databases?](https://stackoverflow.com/a/40329503/3404097) PS Tables represent relationships. "Has", "in", "of", "belongs to" etc are vacuous & poor name choices--they just say some things are somehow related, which we already know. Pick a name/phrase that captures what the relationship *is* and states what a row states when its column values are so related. Tho sometimes the relationship is just "is a type/sort/kind of". "product *p* is in category *c*" – philipxy Oct 04 '17 at 06:57
  • Possible duplicate of [Are There Bidirectional Relational Databases?](https://stackoverflow.com/questions/40096518/are-there-bidirectional-relational-databases) – philipxy Oct 04 '17 at 07:09

2 Answers2

0

Since many-to-many is bidirectional, there really is no need for consideration of a "correct direction". As for the design, I would create product_category as a static table with only primary keys; the products table with its own primary key column(s) such as Product ID; and a third association table which foreign keys the primary keys from the two tables.

PRODUCT_TAGS
PROD_ID|CAT
21|Heating
21|Security
25|Heating
37|Lighting
37|Security

shasan
  • 178
  • 2
  • 13
0

I think you have a one-to-many relationship instead of a many-to-many.

Whenever I look at relationships of any type, I ask myself a question about the tables (using your table names):

Does a product have many categories, or does a category have many products?

If the first point is true, then the ID from the product table goes into the category table. Otherwise, the ID from the category table goes into the product table.

If both is true (a product has many categories and a category has many products), then you'll need a joining table.

I'm assuming that a category has many products, but a product can only have one category. If that's true, you would add a category_id to your product table.

If it's a many-to-many relationship, your table would look like this: product_category_map (product_id, category_id)

Hope that answers your question. I recently wrote an article on normalisation that covers the concept of one-to-many and many-to-many relationships as it can be a confusing concept. (Disclosure: I own that website).

bbrumm
  • 1,342
  • 1
  • 8
  • 13