Database Design: Best way to impliment an Active Advertisement?

Hey Codesmith/NetTiers community:

 I have a question about the easiest way to structure and impliment the following relationships in NetTiers... Or more looking for your thoughts on how it should be done.

 The entities relevant to the question are:

1. Advertisers
2. Advertisements
3. States
   

An advertiser can list 1 Advertisement per state and each state can have up to 4 advertisements within a given week (Starting midnight Sunday and ending a week later).  Assuming no bridge tables are built yet (but they can be if needed), how would you suggest I impliment this... (new entities, column lists like 'start date and end date')

 I can get the basic concepts of code, I'm just looking for some psuedo code examples to get the creative juices flowing... One of the main metrics I am trying to consider is to keep User Interface and Interaction at its easiest (in terms of how the user would select which week to list).

Thank you SO MUCH in advance

 -Tom

-- Advertisers
CREATE TABLE [dbo].[Advertiser](
[AdvertiserId] int IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL CONSTRAINT [DF_Advertiser_Name] DEFAULT (''),
CONSTRAINT [PK_Advertiser] PRIMARY KEY CLUSTERED
(
[AdvertiserId] ASC
)
)

-- Advertisement
CREATE TABLE [dbo].[Advertisement](
[AdvertisementId] int IDENTITY(1,1) NOT NULL,
[AdvertiserId] int NOT NULL,
[UsStateId] int NOT NULL,
[StartDate] datetime NOT NULL,
[AdName] varchar(50) NOT NULL CONSTRAINT [DF_Advertisement_AdName] DEFAULT (''),
[AdTitle] varchar(150) NOT NULL CONSTRAINT [DF_Advertisement_AdTitle] DEFAULT (''),
[AdText] varchar(MAX) NOT NULL CONSTRAINT [DF_Advertisement_AdText] DEFAULT (''),
CONSTRAINT [PK_Advertisement] PRIMARY KEY CLUSTERED
(
[AdvertisementId] ASC
)
)
GO

-- US States (I shine away from using the word 'State' in this context
CREATE TABLE [dbo].[UsState](
[UsStateId] int IDENTITY(1,1) NOT NULL,
[Name] varchar(35) NOT NULL CONSTRAINT [DF_UsState_Name] DEFAULT (''),
CONSTRAINT [PK_UsState] PRIMARY KEY CLUSTERED
(
[UsStateId] ASC
)
)
GO

-- FK Constraint from Advertisement to Advertiser
ALTER TABLE [dbo].[Advertisement] WITH CHECK ADD CONSTRAINT [FK_Advertisement_Advertiser] FOREIGN KEY([AdvertiserId])
REFERENCES [dbo].[Advertiser] ([AdvertiserId])
GO

ALTER TABLE [dbo].[Advertisement] CHECK CONSTRAINT [FK_Advertisement_Advertiser]
GO

-- FK Constraint from Advertisement to State
ALTER TABLE [dbo].[Advertisement] WITH CHECK ADD CONSTRAINT [FK_Advertisement_UsState] FOREIGN KEY(UsStateId)
REFERENCES [dbo].[UsState] ([UsStateId])
GO

ALTER TABLE [dbo].[Advertisement] CHECK CONSTRAINT [FK_Advertisement_UsState]
GO

-- Unique contraint which will support the requirement of
-- 1 Advertiser >> Advertisement >> per State (per week? rigth?)
CREATE UNIQUE INDEX IDX_Advertisement_AdvertiserId_StateId_StartDate
ON [Advertisement] (AdvertiserId, UsStateId, StartDate)

-- Unique contraint to make sure we don't get any duplicate states
CREATE UNIQUE INDEX IDX_Name
ON [UsState] ([Name])

So here is a first round shot an an example. A few things worth pointing out;
The unique FK contraint to allow a specific advertiser one advertisement per state per period.
The logic for allowing 4 per state per period would probably be handled in the BL maybe by a config setting.

Not a big fan of triggers, but you could attempt to implement a trigger on INSERT for the advertisement table to assure there aren't already 4 ads.
I haven't seen a check constraint do this, query, but it might be possible.

Good luck!
Ry

 Ry,

 Awesome Design.  Thanks for the code too.   I like the idea of just using the start date... I thought before of keeping track of the start and end, but the timespans got too much to deal with along with the 4 or less requirement.

Think I for it, but any other imput would be appreciated.

 -Tom

No sweat Tom.

I know you are looking for someone else to chime in as well but I feel talkative...

The main reason for only using a start date is the fact that you have flexibility if you ever want to let ads run on a two week increment, or 3 day. You could also get silly with it to add one additional table, AdvertisementLength, where you could could explicitly set different ad period cycles. Or even more simple add a RunningDays field to the Advertisement table. That would be very flexible, and maybe a config setting to store the "default" number of days an ad runs.

One thing that alwas makes me laugh, and probably insult some by doing so, is when a client states, "It could happen, but it never will!". I think to myself, yeah right.. It will... At some point, it will... So I try to give as much flexibility to a design as possible.

Hope I helped!
Ry

There is a way to use a check contraint to query... Learned something new. You can use a CHECK CONSTRAINT on the dbo.Advertiement table.

You first have to create a Scalar function like the one above. It simply returns the COUNT of records that meet your contraint of 4 ads per state per week.

RETURN @@Result

END
GO

Then add the CONTSTRAINT to the table

 Ryan,

 Thank you for the responses and help on this one.  I have the information in place and will be posting the live site soon.  I will give you a link when everythings in place (if you are interested).

 Thanks again!

 -Tom        

No prob Tom 

Yeah, totally curious! Let me know!
RA