Member Avatar for 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

  • 2 Contributors
  • 6 Replies
  • 129 Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by darkagn

Dani AI

Generated

Good start from and a sensible question from . The core idea to treat scheduling as a set of records rather than trying to cram months or zones into single cells is the right direction. A few practical design points that build on the thread and address the sign-off/change-tracking concerns:

Keep creative (the ad) and schedule separate

  • Store each artwork submission/version independently (version number, file/id, who submitted it, timestamp, approval status, approver and approval timestamp, notes). Do not overwrite previous versions — create a new version row when changes happen.
  • Link schedule entries to a specific artwork/version so the system can answer "which exact file was approved for printing in May in Zone X."

Model placements as one row per unique run combination

  • For reporting and integrity, represent each unique combination (month, zone, size, ad-version) as its own placement record. Avoid comma-separated lists or packed values — they kill queries and reporting.
  • If zones carry extra attributes (pricing, deadlines, page assignment), model those in lookup tables or a zone-pricing table rather than bolting them into a single cell.

Sign-off and audit

  • Keep explicit fields for approval state, approver id, approval timestamp and a changelog. Prefer soft-delete or status flags over hard deletes so historical footprints remain intact.

Integrity and performance tips

  • Enforce foreign keys, add unique constraints that reflect business rules (prevent duplicate placements), and index fields used in lookups (month, zone). At high scale consider partitioning by year/month (MySQL supports partitioning) and wrap bulk scheduling operations in transactions to maintain consistency.

Common pitfalls to avoid

  • Don’t mix schedule with creative metadata, don’t store arrays in cells, and don’t cascade-delete historical records. Designing with separation of concerns, versioning and explicit placements keeps sign-off workflows, proofs, and print audits reliable and easy to query.

Recommended Answers

All 6 Replies

Member Avatar for 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
Member Avatar for 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!

Member Avatar for 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.

Member Avatar for 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).

Member Avatar for 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.

Member Avatar for 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...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.