Ad Booking / CRM System

[paulinus]

I'm trying to create a simple advertisment booking system for a community newspaper. The paper is published (printed) every two weeks and relies on local small businesses taking out small ads.

We want to keep track of all of our customers, and their bookings both past and future.

Each issue has it's own number number (e.g. Issue 138 will be published on 21 March).

I'm just wondering what is the best design for each table of data?

Should I have a table for each issue? I know I need a table of customers. I'd also like to be able to add notes to each customer record (for phone calls etc.).

Structure is what I looking for!

Any help appreciated.

[timothybard]

I would recommend, at a minimum, having the following tables:

Issue: ID
Client: ID, Name, etc
Ad: ID, Issue Id, Client ID, Date Booked, Price, etc

You would not have each issue as a separate table, but rather each issue would be a different record in the Issue table. You then track which Ads each client has booked by using the Ad table to track the client and which issue they have an ad booked in.

Some additional tables that may be helpful:
Client Phone Contact Names: ID, Client ID, Contact Name, Notes
Client Phone Number: ID, Client ID, Phone Number
Client Phone Address: ID, Client ID, Street Address, City, State, Zip

You can also track phone number, address, etc for each Contact as well. The database can certainly be more fine tuned, but I'd need more information about the ads and booking process to help further.

[bigakis]

You may also need the following tables:
ad_size:id, name, page_factor (whole page(1), 1/2 of the page(0.5), etc)
ad_kind:id, name (single color, 2 colors, full colors, etc, leaflet)
ad_page:id, page (front_page, left page, right page, last paget, cover page, etc)
In the ad table add the fields:
ad_sizeid, ad_kindid, id_pageid (add them as PK's)
This way you can have one customer having many ads in the same issue. By summing the page_factor field for each issue you can have the proportion of total pages/ad pages.