Best way to handle a many to many relationship?

[tibberous]

I am making a multi-user system where there are multiple stores and multiple products. What makes this a little hard is that certain users will have several stores, and will be able to assign one product to many stores (thus the many to many)

 

 

 

Generally you would make a binding table, but I'm wondering if it wouldn't be simpler to give products a varchar field called stores and just store the store ids like "-56-57-68-"

 

A product can belong to multiple stores, but realistically it will never belong to 5-10. The only downside I can see is that, to select the products in a store, I'll need to use like '%-57-%'

 

Even still, I wouldn't think it would matter much. Worse case, I could rewrite it as `Store1`, `Store2`, `Store3` or even take heavily trafficed stores and create them as seperate boolean columns.

 

 

Just looking for general advice on how to handle this. Honestly, I might be over thinking it... I could probably do it however and it would work fine?

[trq]

Generally you would make a binding table, but I'm wondering if it wouldn't be simpler to give products a varchar field called stores and just store the store ids like "-56-57-68-"

 

No, it definitely won't be simpler. The idea of storing concatenated data in a database designed to store data with relationships makes my hair stand on end.

[Barand]

I'm with trq. It will cripple your ability to query the database

[HenryCan]

I agree with trq. Mind you, I used to teach Database Design (for money) and the best approach for many-to-many relationships is to build an association table, sometimes called an intersection table. I've never heard it called a binding table before but I'm guessing it's the same thing. In any case, it would look like this, assuming you have a real many-to-many relationship. In other words, if a given product could be in many stores and a given store could have many products, which would be the way most retail chains work, you'd organize the tables like this:

 

PRODUCT Table: one row for each distinct product - primary key = product_id

Product_ID Product_Description

1 Toaster - 4 Slice - Hamilton Beach

2 Computer - Laptop - Model K54N - ASUS

3 Bicycle - 10 speed - Schwinn

 

STORE Table: one row for each store - primary key = store_id

Store_ID Store_Location

US1 West Valley Mall - Fresno - California

UK1 27 Hilldale Lane - Brighton - UK

DE1 Meyerstrasse 15 - Berlin - Germany

 

[Note that the description information in the PRODUCT and STORE tables is likely going to be more elaborate and broken out into several columns but the key thing is that there will be a primary key consisting of something that uniquely identifies each product or store.]

 

Then, your association/interesection table.

PRODUCT_STORE table : one row for each store in which a given product is carried - primary key = product_id || store_id

Product_ID Store_ID Quantity

1 US1 50

1 DE1 10

2 UK1 20

2 US1 85

3 US1 14

3 UK1 27

3 DE1 15

 

[Note that I've added some extra data that doesn't belong with either the product or store directly just to show you how useful that association table can be. The quantity represents how many toasters, bicycles or whatever are situated in each store. It's the inventory so that a customer can determine if you have that item in a given store in sufficient quantity for him to buy as many as he needs. (I'm picturing a guy who wants to buy several laptops for his business rather than just one for himself.) It's important to note that the quantity is not the quantity of all laptops or bicycles that your company owns, just the number that you have in a given store. But if you want to know how many you have in your whole chain, you can easily add them up from the information in the Quantity column.]

 

It's not that putting a concatenation of locations on the product table is impossible but it's a heck of a lot harder in the long run. A professional database designer MIGHT go the way you want to go if the costs and benefits make it a reasonable approach but that isn't going to happen that often. Professionals are pragmatists and may choose not to employ full normalization of data but only if they do a proper cost-benefit analysis that says it makes sense to go that way.

[tibberous]

...

 

The one really interesting thing I found about your post is that, rather than use the association table for straight 1-1, you gave it a quanity field (I would have put quanity as a property of products without really thinking about it)

 

It's funny because sometimes you make a thread to ask a question and kind of make your mind up while typing the thread - I think I'm going to reasses things now.