Help defining tables/Primary Keys

Question

I'm designing a database for a franchiser. My skill level is intermediate at best (I just work in the franchiser's office). This database must define store locations and franchisees. So I know I will need a "store" table and a "franchisee" table. The "store number" will be the primary key in the store table. One franchisee can own multiple stores. I figured I could just assign a "Franchisee ID" as a primary key in the franchisee table to like franchisees to stores. Here's my issue: Each store can be owned by up to 4 franchisees. So I'm stuck on how to define all that. I'm not sure I can do multiple franchisee ID's and even if I could, often, stores are only owned by 1 or 2 people. That means many blank fields. Also, I'm unsure of how it will all come together when I need to pull queries. Any suggestions on the most logical way to do this?

Thanks!

Answer 1

You need a join table, create a table called franchisee_store with 2 id fields, 1 will be the id of the store and one will be the id of the franchisee.

here is a good example for you.

Answer 2

What you need is a many-to-many joining table. This is the correct way to do this in third normal form.

Example:

franchisees:
    farnchisee_id
    other stuff
stores:
    store_id
    other stuff
franchisee_stores:
    frachisee_id
    store_id

It's never generally a good idea to add arrays of attributes to a table since that can be handled better with a joining table. As you've already stated, an array of four franchisees in your stores table would waste space for those stores with less and, more importantly, limit the number of franchisees that can own a store arbitrarily.

Answer 3

Okay, so for the stores, you same to have a valid primary key already - the StoreNumber. So you "Store" table will be something like

Store     StoreNumber INTEGER   PRIMARY KEY
          StoreName   STRING 
          ......

Then you have another table Franchisee which will hold the franchisees and their info - primary key will be the FranchiseeID

Franchisee  FranchiseeID    INTEGER  PRIMARY KEY
            FranchiseeName  STRING
            (other fields)

To join those two, since you can have more than a single owner, you need a StoreOwner table, something like this:

StoreOwner    StoreNumber    INTEGER  FOREIGN KEY to "Store"
              FranchiseeID   INTEGER  FOREIGN KEY to "Franchisee"

The combination of both fields (StoreNumber, FranchiseeID) will be your primary key in StoreOwner.

That way, you can have any number of owners for a given store.

Answer 4

I'm wondering if anyone has any ideas on how to enforce the "4 franchisees per store" rule in a Jet/ACE data store. Certainly, A2010 adds new table-level data macros, which can function something like triggers, allowing you to define a data macro for the join table that could enforce that business rule.

But before A2010, there's no engine-level way to do this -- you have to put some of that logic in the application itself.

I'm thinking that one way to do this would be to add a 3rd column to your join table that is a franchisee number. As part of the PK, it would be required, and if you put a validation rule on it like "Between 1 And 4". That would not automatically increment so you'd have to handle that in code, but it would prevent adding a record that didn't conform to the rule.

Does anyone have any suggestions on any other approach to solving this part of the problem? I'd love to hear if there were some way to model it with tables lacking triggers and multi-record table-level constraints. It would have to work in Jet/ACE for it to be viable here, though.