Hello All,
I seem to be having an issue with a many to many relationship database or how I like to call it a many to many to many...... j/k.
I am wondering if I can get some input for this very long problem.
I work for a direct mail company that has franchised out many areas that we mail to. We mail once a month and have many customers who run in mutiple zones with multiple ad sizes, multiple months.
The problem that I am having is adding cross reference tables to tie this all together. Here is what I have.
Franchisee_Table
franchisee_id
first_name
last_name
franchisee_number
Zones_Table
zone_id
zone_name
zone_state
franchisee_id
Category_Table
category_id
category_name
Adsize_Table
adsize_id
adsize_name
adsize_value
Business_Table
business_id
business_name
phone_num
address
city
state
zip
Ads_Table
ad_id
franchisee_id
category_id
adsize_id
business_id
ad_name
SHould ther be a Ads_month table indication the ads and the months that it is running in?
Should each zone have its own table to insert the ad into (there is 33)?
Now one of the main purposes of this database is for simplification (obviously). Currently this is being tracked by a spread sheet. Here are the details.
Mailing areas are broken into zones.
Franchisees can own many zones.
Customers can choose to mail in multiple zones.
When franchisees sell out of their zones into a different franchisees zones that has to be tracked and they are charged for that. So for example:
Franchisee A owns zone 1
Franchisee B owns zone 2
Franchisee A goes into a customer in zone 1 and that customer wants to mail in zone 1 and zone 2. That needs to be tracked because they have to pay to put that customer in zone 2.
Currently franchisees are required to submit a cross sales form of only customers that are going into zones that are not their own and listing the zones.
The current tracking system is this:
Each month has its own spreadsheet.
Each franchisee has their own tab on that spreadsheet.
When the cross sales sheet comes in and franchisees has a zone listed for a particular customer that is buying a zone that is not their own we have to ad it to the owners spread sheet tab along
With the selling franchisees number, category, etc. This also lets the franchisee know how many ads will be placed in their book from an outside franchisee.
The way I would like this to work is when the cross sales sheet comes in it gets entered into a form. On this form I would like to be able to enter in the selling franchisees number, the business
Name, category, ad size, what month(s) they will be running (could be multiple), and select check boxes for what zone(s) they are running in (could be multiple).
Then be able to select a franchisee and see what ads are running in other zones other than their own.
Select a zone to see what cross sales are in it for that month and who sold them.
Ability to easily look up passed months.
The reason for the normalization the way I have it is, if I can get this to work you can see how this could be expanded upon.
I hope I haved explained this well.
Any help/advice would be greatly appreciated.
Thank You.