MySQL Forums
Forum List  »  MySQL Workbench

Re: Need modeling help
Posted by: Mike Lischke
Date: April 06, 2009 01:21AM

Hey Richard,

that's quite an easy scenario. What you essentially need are 3 tables (relations):

1) A list of all stores, identified by an id, so they can be referenced easily.
2) A list of product items, essentially the db representation of your product catalog (also with an id).
3) A stock list, which provides the relationships between the shops and the product items.

The stock table gets records for each store and each item. This would be the primary key (storeId + itemId). There will be n:1 relationships (db term: foreign keys) from your stock table to both the stores table and the item table (i.e. you can have zero or more records in your stocks table which reference a single store or item). You'd place primary keys on the store and item ids in their respective tables.

Since you want to look up which store has a given item you would also add an index on the itemId column in the stock table (non-unique as item ids can appear more than once). This way you can easily query the table by saying:

select * from stock where itemId = xyz;

and will get a list of all stores that offer this item currently (provided you update the stocks table properly, so that it reflects the reality). By placing an index on the itemId column in the stocks table you increase processing time for queries like the one above significantly.



Mike Lischke, MySQL Developer Tools
Oracle Corporation

MySQL Workbench on Github:
On Twitter:
On Slack: (#workbench)
Report bugs to
MySQL documentation can be found here:

Options: ReplyQuote

Written By
April 05, 2009 08:10PM
Re: Need modeling help
April 06, 2009 01:21AM
April 06, 2009 06:54PM
April 07, 2009 10:18PM
April 11, 2009 08:36PM
April 14, 2009 01:34AM
April 17, 2009 07:03PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.