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 customer 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
Categories_Table
cat_id
category_name
Adsizes_Table
adsize_id
adsize_name
ad_size
Businessname_Table
businessname_id
business_name
Zone_Table
zone_id
zone_name
zone_state
Ads_Table
ad_id
ad_name
Monthsrun_Table
month_id
month_name
Now one of the main purposes of this database is for simplification (obviously). Currently this is being tracked by a spread sheet. Here ae 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.
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.
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.
Any help/advice would be greatly appreciated.
I would also like to say thank you to Darkagon who had helped me with a similar problem.