Need help with database setup

[ctyler]

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.

[weekend00]

Category_Table
category_id
category_name

Since you have only category_name in this table without other details or description, category_table is not necessary. Just put the column category_name in other tables where applied.

[ctyler]

Hi weekend00,
Thanks for the reply. One of the reason I have these broken out that way is for flexibility. One thing I can tell you about the business of advertising especially in direct mail is that it changes alot. Having no descriptions in the category field may change and be expanded upon.

My real problem really breaks down to ease of input. Lets say a customer purchases a half page ad in 5 zones. They want to advertise for three months in those same zones. Should each zone be its own table that the ad would be inserted into?

What if they run for three months and each month they want to run in different zones?

I am hopeing somone can take a fresh look at this and maybe have a better idea or a more elegant solution.

Thank You

[weekend00]

Nobody can design a one-size-fit-all system. If there is no foreseeable requirement, you don't need to put too much work about flexibility( althrough it is good for flexibility with little effort).

you should not include franchisee_id in Zone table, zone table should maintain only information of zone.

you can add 3 fields to ads_table: zone_id,AD_from_date and AD_to_date. There will be one record for each franchisee_id,category_id,adsize_id,business_id,zo ne_id in each period.

[ctyler]

Hi weekend00,
THe categories table is important because they get added to all the time. I thought I could make that a lookup on the ads table. The categories does get added to quite frequently and it is important that it stays consisitant.

I definately do not want to shoot holes in any ideas but his is why I am stuggling so badly with this. I am almost bald.

The reason for the franchisee id in the zone field is to indicate who owns that zone. Would it be better to have a FranchisseZone_Table to relate the 2? Do you think adding a zone field to the ads table would work. Each zone could have its own column with a yes/no data field for each? THat seems like a good idea but how would I look up whos ads are running in that zone that does not belong to the franchisee who owns that particular zone?

Adding three fields is a good idea. The only issue with that is not all ads run consecutively. Some run every other month. I could put the months in seperate columns in that ads table.

I really appreciate your input weekend00. Bouncing ideas back and forth is very helpfull.

[weekend00]

For sure you can keep catagory table since there is a posible expanding.

Maybe I misunderstood, are there more than one franchisee in one zone? if there only one, you should keep it.

The only issue with that is not all ads run consecutively. Some run every other month. I could put the months in seperate columns in that ads table

I am not sure what you want to do with table ads_table. do you think they are same Ad if the Ads are in different zone, or different month. in my mind, they are different Ads and should have seperate records in the table.

[ctyler]

Hi weekend00,
I know it is confusing wich is why I am having such a hard time applying logic to it. Maybe a little more information is in order and hopefully this is not TMI.
Lets say you bought a franchise. A franchise consists of a number of zones that you mail a coupon book to. Each zone gets mailed a different book. Lets say for simplicity sake you only bought 1 zone. The minumum book that you can mail is a 16 page book. From there the number of pages that you can print goes up by a multiple of 4 to a maximum of 32. The ad sizes can be 1/6, 1/3, 1/4, 1/2, full. You went out and sold 16 pages worth of advertising. You then find out that another franchisee sold an ad in your zone and it is ony a 1/4 page. That just bumped you up to 16.25 pages wich means you now have to print and mail a 20 page book. You would need to know that immediately so you can get that other 3.75 pages sold.

Now there is another reason for this. You can sell the ads in your book for pretty much what ever you like. You pay the franchisor for print and mail of your book. It does not matter to us what you sell your ads for. It does not change the cost of your print and mail. If you sell into another zone like the other franchisee sold into yours you would pay a flat fee or "cost " of that ad space. That is important because all the cross sales are handled through the office so you don't have to worry about paying another franchisee for space in his book and you don't have to worry about collecting payment from another franchisee who sold in your book. If you sold into another franchisees zone we would just ad the "cost" of the space sold to your print and mail and take it off of the recieving franchisees print and mail. It keeps the peace that way. By the way I am using Zones and Books interchangeably.

As soon as you sell into another zone that is not your own your required to send in your cross sales report noting what business, size of the ad, category and what zones. I need to be able to plug this information into the Access and hopefully be able to do the following.

We need to be able to give you a report at any time showing you what has been sold into your book/zone from an outside franchisee.

We also need to look at a report by franchisee and see how many cross sales they made. Meaning what ads they sold into zones other than their own so we can adjust there bill according.

Regarding the ads table:
If an ad is a half page and is going to several different zones in the same month it will be the identicle ad. Unless of course a customer wants a tracking number in there coupons for each zone then it would be different for each zone. That really doesn't matter to much as far as this scenario is concerned. It is a half page no matter what is on the actual ad. However, they can run a 1/4 page in one zone/book and a half page in another zone in the same month. The does matter because it affects the size of the book and would have to be tracked seperately.

The reason why categories is important is because when you go to paginate (figuring out what ads go were in your book) you may not actually see the cross sold ads. This is important becasue you do not want to place like businesses next to each other on the same page. Also, you cannot always tell the category by the business name.

I hope this helps explain a little bit better of what I am trying to do.