Need database architechture advice for Franchise Tracking system

[nerdcore]

I need to create a Franchisee tracking system for a client. I am working for the world headquarters of a Franchise System and they have 100+ franchise companies (just think McDonald's on a smaller scale). They have a database system in place now but it was not designed to properly accomodate their business and the way they want to track, and report on their franchisees.

The basic premis is: there are multiple businesses (the franchises) which are owned by one or more people/companies (the franchisees). One business can be owned by multiple people, and a person can own multiple businesses. I need to be able to track who owns what, who is married to who, and who is a business partner of who, and on which franchise this relationship exists. I also need to track the history of each franchise, for example if person "A" sells his franchise to person "B" I cannot lose the information about person "A" including the business address and all history info associated with the record.

I'm looking for some advice on the table structure to accomplish the above. Here is what I have so far (and the problems I am facing). The main problem I am seeing so far is where to store the owner specific information when there are more than 1 owner. I need this info to not only be associated with the franchise company, but with each owner. AND when this frachise is re-sold, I need to preserve it and start a "new" owner (or group of owners) record.

Tables:

tbl_FranchiseInfo - contains non-owner specific information about a franchse. i.e. Franchise Number (assigned by the corp office), Franchise Location, Territory covered, area population, etc.

tbl_OwnerInfo - Each person will have a record here. So a married couple buying a franchise will have two records in this table, one for each person. These two records will need to be related which should be simple enuf by putting a field "Spouse_ID".

tbl_Franchise_Owner_Xref - contains 3 fields: Unique_ID, Franchise_ID, Owner_ID. This table ties an owner to a Franchise. But this is where I am getting lost. It only ties ONE owner to a franchise at a time.

This is where i need some advice. I need to enter information now about this specific instance of franchise "ownership". i.e. where do I put information about the business address, phone number, etc.? Remember it needs to be preserved if this franchise is sold. I could just make another table which linked off tbl_Franchise_Owner_Xref.Unique_ID but then it would only tie to ONE owner, not the whole group of owners, which in some cases is up to 6 different people. I don't want to put the information directly in the tbl_FranchiseInfo table as it will need to be erased if the franchise is sold.

I am open to any suggestions, and maybe I am going about it wrong with the above table structure.

Any help any of you database Gurus can offer would be appreciated!

 

[lespaul]

First off, read 'The Fundamentals of Relational Database Design'. This is a MUST READ for anyone creating databases.

Secondly, after you read that you need to decide what you are storing information about.

You have people, businesses and franchise owners. These are three separate entities.

Franchise owners can be people or businesses

businesses can be owned by one or many people

people have connections to other people

Does that sum it up?

tblPeople
PersonID
LName
FName
(etc. anything to do with the PERSON)

tblBusiness
BusinessID
BusinessName
BusinessAddress
(anything else to do with the business EXCEPT which person or people it is associated with)

tblBusinessOwners (this is a many to many table that will allow you to track the many owners of a single business or the single owner of multiple businesses)
BusinessID
PersonID

tblFranchiseInformation
FranchiseID
(anything else to do with the franchise EXCEPT who it is owned by)

tblFranchiseOwners
FranchiseID
BusinessID***


***Here's where you will need to make a decision. If this was up to me, I would force the FranchiseOwner to be a business. If a PERSON owns the franchise I would create a business ID for that person. For example, Joe Blow buys a franchise. Joe Blow is a person and doesn't have a business (although I'm sure that most of the franchise owners are businesses, the tax situation for owning a franchise as a sole propreitor would be INSANE!!), I would create a business:

Joe Blow, Sole Propreitor

You other choice would be to have:

tblFranchiseOwners
FranchiseOwnerID
FranchiseID
BusinessID
PersonID

and either fill in the BusinessID or the PersonID (but not both!)

About the tracking of relationships, do you only need to track spouses or are there other relationships you need to track?

In order to track the history of the Franchise ownership, you might want to add AcquireDate and DisposalDate in the table tblFranchiseOwners.

Hopefully this has given you some ideas on where to go with this. Be sure to read the Fundamentals, you will be ALOT happier in the future with a normalized structure.

HTH

Leslie