Reddit mentions of Data Model Patterns

/u/chammit gave you the best answer. I'd like to expand on it so that you have a few ways to look at 'linking' tables.

I've worked on distributed order management systems and a bunch of other stuff. The basic relationship you need to understand is the many-to-many relationship. To use your example, a product has a set of attributes. One type of product is a screwdriver. A specific screwdriver may be in one and only one inventory location.

Screwdrivers can be in one or more inventory locations. In one sense, this sounds like one-to-many, but it doesn't scale. If we only had one product and could guarantee that we'd never have more than one product, the inventory location would just be another column on the product table. But this is not realistic.

Products may exist in one or more inventory locations.

An Inventory location may stock one or more products.

These two statements declare the many-to-many relationship. Many-to-many relationships are always resolved through a 'link' table that maps the PK from one table in the relationship to the PK in the other table. Each product in the product table might have one record in the link table for each store.

Which leads to this question: how many screwdrivers are in the Canadian store? Which leads to this: where should the model store the value for how many screwdrivers are in a store? That value belongs in the link table. So do pricing, the effective dates for sales, replenishment thresholds (time to reorder number), etc.

Here are some synonyms that I've run across in specific situations. This will help you understand what people are talking about. Just remember that all these tables resolve a many-to-many relationship, with one potential exception.

Here's the possible exception: Crosswalk table. This is used to maintain the mappings of one system's names for things to another system's different names for the same things. It's used in integration APIs, ETL for data warehouse and integrations feeds. It's more often the case that this table only has one mapping record for each term, to no many-to-many relationship exists, even though it could.

Other synonyms: bridge table, associative table.

Note that if you look at an invoice detail table, it is resolving the many-to-many relationships between several entities; product, customer, store, date. The same can be said for any detail table in a master-detail model, any child in a parent-child model. The phrase, 'master-detail', I believe is common in GUI design, while 'parent-child' is common in backend development. They mean the same thing from the point of view of the table structure.

Your modeling skills are off to a good start. This is because you asked the right question: "How many screwdrivers are at the Canadian store?" I attempted to share how I would break down your question to arrive at a modeling solution.

Edit: My recommended book: https://www.amazon.com/Data-Model-Patterns-David-Hay/dp/0932633749

For your particular application I would look at OpenStreetMaps. Otherwise...

David Hay's

• [Enterprise Model Patterns][1]. This is a beast of a book, but has some great patterns.
  
• [Conventions of Thought][2]. More stuff on MRP.
  
• [A Meta-Data Map][3] . Haven't read this one.
  
  Len Silverston's
  
  
• [Data Model Resource Book Vol. 1][4]. Your main data model patterns.
  
• [Data Model Resource Book Vol. 2][5]. Case studies by industry.
  
• [Data Model Resource Book Vol. 3][6]. A deeper explanation of Vol 1.
  
  Michael Blaha's [Patterns of Data Modeling][7]. This one has some interesting temporal, graph, and tree models.
  
  Martin Fowler's [Analysis Patterns][8]. This one skims some of the other patterns, but gives accounting a solid treatment.
  
  They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on [safaribooksonline][9].
  
  Also, OASIS's [Universal Business Language][10], schemas
  
  
  [1]: http://www.amazon.com/Enterprise-Model-Patterns-Describing-Version/dp/1935504053/ref=sr_1_1?s=books&ie=UTF8&qid=1346950468&sr=1-1&keywords=enterprise%20model%20patterns
  [2]: http://www.amazon.com/Data-Model-Patterns-David-Hay/dp/0932633749/ref=pd_sim_b_2?ie=UTF8&refRID=1PQPGE4E6T2RPR2XTN80
  [3]: http://www.amazon.com/Data-Model-Patterns-Metadata-Management/dp/0120887983/ref=pd_sim_b_3?ie=UTF8&refRID=1PQPGE4E6T2RPR2XTN80
  [4]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=pd_sim_b_3?ie=UTF8&refRID=08T9TEZJNZM2EMKZV3AB
  [5]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471353485/ref=pd_sim_b_3?ie=UTF8&refRID=1D5TDG7479G7TQMBPNWF
  [6]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0470178450/ref=pd_sim_b_4?ie=UTF8&refRID=08T9TEZJNZM2EMKZV3AB
  [7]: http://www.amazon.com/Patterns-Modeling-Emerging-Directions-Applications/dp/1439819890/ref=sr_1_1?s=books&ie=UTF8&qid=1346950554&sr=1-1&keywords=patterns%20of%20data%20modeling
  [8]: http://www.amazon.com/Analysis-Patterns-Reusable-Object-Models/dp/0201895420/ref=sr_1_1?s=books&ie=UTF8&qid=1346961699&sr=1-1&keywords=analysis+patterns
  [9]: http://my.safaribooksonline.com/search?q=data%20model
  [10]: http://en.wikipedia.org/wiki/Universal_Business_Language