In Need of Some Advise On DB

[ctyler]

Hello all. Am new to the forums and this is my first post and I am in need of a little help.

I am in the planning stages of building a pretty involved web application. This question needs some background info first.
This is for a direct mail company (a pretty large one). We mail a coupon book once a month. Customers can sign up for one month, several concurrent months and several nonconcurrent months. Ads can be several sizes.

We design the ads. I need to track the ads coming in and out (when they come in for changes) and when they are signed off on every month (meaning they are good to go to print).

The question is what is the best way to trach the ads. Should there be a seperate months table?

CT

[darkagn]

The question is what is the best way to trach the ads. Should there be a seperate months table?

One approach might be to have an ads_months table that links each advertisement to the month(s) it was run. This would require three columns, ads_months_id (numeric PK), ad_id (numeric FK), month_run (date). Then when you want to know what ads were run during january 2010, use something like the following query:

select ad_id, month(month_run), year(month_run) from ads_months
group by ad_id
having month(month_run) = 1 and year(month_run) = 2010

[ctyler]

One approach might be to have an ads_months table that links each advertisement to the month(s) it was run. This would require three columns, ads_months_id (numeric PK), ad_id (numeric FK), month_run (date). Then when you want to know what ads were run during january 2010, use something like the following query:

select ad_id, month(month_run), year(month_run) from ads_months
group by ad_id
having month(month_run) = 1 and year(month_run) = 2010

That is a great idea darkagn. I think that would work nicely!

[ctyler]

One approach might be to have an ads_months table that links each advertisement to the month(s) it was run. This would require three columns, ads_months_id (numeric PK), ad_id (numeric FK), month_run (date). Then when you want to know what ads were run during january 2010, use something like the following query:

select ad_id, month(month_run), year(month_run) from ads_months
group by ad_id
having month(month_run) = 1 and year(month_run) = 2010

Since each ad can run in multiple areas or "Zones", how would you suggest that be handled? I was thinking maybe a ad_months_zones table that would have the columns for ads_month_id and zone_id columns? There is a seperate zone table. Since ads can run in multiple zones it would have to be inserted into this table as many time as the amount of zones that the ad is actually running in? Do think there is a more elegant way of doing this?

I really appreciate your advice darkagn.

[darkagn]

Instead of creating a whole new table, I would add a zone_id field (numeric FK) to your ads_months table. Adding your new table is less efficient because you need to link back through the ads_months table for a select anyway, and each insert into the ads_months table would need a second insert into the ads_months_zone table (same for updates).

[ctyler]

Instead of creating a whole new table, I would add a zone_id field (numeric FK) to your ads_months table. Adding your new table is less efficient because you need to link back through the ads_months table for a select anyway, and each insert into the ads_months table would need a second insert into the ads_months_zone table (same for updates).

hmmmm. If ads can be entered in for multiple months and multiple zones at one time, wouldn't it have to be a row inserted into that table once for every unique combination between the months and zone combinations or would I have to have the list of all the months into one cell and all zones into one cell? I may be over thinking this.
Man! This is the toughest part of this whole project.

[darkagn]

A month that is run over three months in six zones will result in 18 records in the ads_months table. So really you probably want the zone_id to form part of your primary key in that table too? (just a hunch, I'm not sure either). I still think that this is the most efficient way to go in the long run though, because really you are only linking the zone_id back to the month for the ad. If that zone_id was dependent on other things, then it might be wise to consider another table...