Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200.

My question on DB design, is how do i keep track of which products each store has?

  • example

    • store 1 has products 1, 5, 10, 200
    • store 2 has products 2, 5, 7, 100
    • store 3 has products 1, 7, 10, 300
    • etc...
  • Should I have a column in my stores table that has id's stored as a string separated by a column such as {1;5;10;20} (with the possibility of thousands of entries in the 1 string) ?

  • Should I have another table that is just [id|storeID|productID] (with the possibility of stores(x)products in the table) ?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Your problem can be modelled by an entity relationship diagram (created with dia):

ER-diagram.

There are two entities "product" and "store" and there is an m-n-relationship "stocks" between these entities. "product" has an attribute "productId" that uniquely determines the product. "store" has the unique attribute "storeId" that determines uniquely the store.

I have added the attributes "quanity", "storeAddress" and "productName" to make the model a little bit more complex. These attributes cannot be derived by your description, you can remove it from the diagram and from the resulting tables if you want the design follow your description closely. An n-m-realtionship means that a store can stock different products and a product can be in stock in different stores. We have the following problem desription:

  • Each store has a unique Id and an address
  • Each product has a unique Id an a name
  • a store can stock different products with different quantities

The last sentence can also be formulated in this way

  • a product can be stocked in different stores with different quantities

Such a diagram can be transformed in a relational model. Each entity is a table where the attributes are the columns. The unique identifier is the primary key. The m-n-relation is a table where the primary key is the a (storeId,productId) pair. If the relation has an attribute this attribute is a column in the table.

so we have the following relations (tables)

store("storeId", storeAddress)
product("productId",productName)
stock("storeId","productId",quantity)
share|improve this answer

The first option you present is a "NoSQL" design pattern & doesn't allow for easy querying in a normal RDBMS.

Your second option is the most suitable. Have a 3rd table that tracks which products are in which store. Possibly called "Stock" - you could add a count to this if you wanted to track how many of each item each store has.

share|improve this answer
1  
+1 for Store,Product,Stock (3 tables) –  Warren P Aug 3 '12 at 0:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.