Re: Data structures for time scheduling

John,

I can't tell you how much your post has helped me start to sort this thing out, I was really in the dark there.

Thank you, Thank you :)

On Mon, 24 May 2004 21:46:08 GMT, "John Gilson" <jag_at_acm.org> wrote:

>"Sherman" <noemail_at_none.com> wrote in message news:0fpta0pve10j4sjje1qm9vduicb3h3di83_at_4ax.com...
>> Hi Gang,
>>
>> Hope somebody can help or steer me in the right direction. I know this
>> kinda thing has been done before, but I'm having a hard time getting
>> my head around it right now.
>>
>> What I'm trying to do is create a program to schedule radio
>> programming for a broadcasting friend of mine. Things like news
>> programs, ads, etc. He needs the ability to allocate his radio time,
>> in increments going down to 10 seconds at the lowest.
>>
>> I'm having a real hard time trying to come up with how to properly
>> store the data, especially considering how short the duration of times
>> can be. Does anybody have any experience with this sort of thing?
>>
>> Any help really really appreciated.
>
>I know next to nothing about this industry, so keep that in mind, but do
>listen to a lot of radio. I assume there's a schedule for radio shows,
>e.g., news, music, talk, etc., and a schedule for ads e.g., corporate,
>public service, station announcements, etc. Shows don't overlap
>with each other and neither do ads but ads can and do overlap with shows.
>
>I've used SQL Server's T-SQL dialect of SQL but have tried to keep it
>as close to Standard SQL as possible.
>
>-- Kinds of radio shows
>CREATE TABLE RadioShowTypes
>(
>radio_show_type VARCHAR(20) NOT NULL PRIMARY KEY
>)
>
>-- Each radio show's name, type and any other descriptive data
>CREATE TABLE RadioShows
>(
>radio_show VARCHAR(20) NOT NULL PRIMARY KEY,
>radio_show_type VARCHAR(20) NOT NULL
> REFERENCES RadioShowTypes (radio_show_type)
>)
>
>-- Kinds of ads
>CREATE TABLE RadioAdTypes
>(
>radio_ad_type VARCHAR(20) NOT NULL PRIMARY KEY
>)
>
>-- Each radio ad's name, type and any other descriptive data
>CREATE TABLE RadioAds
>(
>radio_ad VARCHAR(20) NOT NULL PRIMARY KEY,
>radio_ad_type VARCHAR(20) NOT NULL
> REFERENCES RadioAdTypes (radio_ad_type)
>)
>
>-- The show programming is defined as a show's name, its start datetime, inclusive,
>-- and end datetime, exclusive. The DATETIME data type is like Standard SQL's
>-- TIMESTAMP. The integrity checks ensure that an end datetime is always
>-- after a start datetime and that programming doesn't overlap. There might
>-- be other business constraints to enforce, e.g., a show starts and ends on the
>-- hour or half hour.
>CREATE TABLE RadioShowProgramming
>(
>radio_show VARCHAR(20) NOT NULL
> REFERENCES RadioShows (radio_show),
>show_start DATETIME NOT NULL PRIMARY KEY, -- inclusive
>show_end DATETIME NOT NULL UNIQUE, -- exclusive
>CHECK (show_end > show_start)
>/*
>This prevents show programming from overlapping. T-SQL doesn't implement
>subqueries for constraints so this would need to be placed in a trigger.
>However, declaratively, one would have
>CHECK (NOT EXISTS
> (SELECT *
> FROM RadioShowProgramming AS P
> WHERE ((RadioShowProgramming.show_start >= P.show_start AND
> RadioShowProgramming.show_start < P.show_end) OR
> (RadioShowProgramming.show_end > P.show_start AND
> RadioShowProgramming.show_end <= P.show_end)) AND
> RadioShowProgramming.show_start <> P.show_start AND
> RadioShowProgramming.show_end <> P.show_end))
>*/
>)
>
>-- Periods of time where there's no show programming
>CREATE VIEW RadioShowGaps (show_start_gap, show_end_gap)
>AS
>SELECT MAX(P2.show_end), P1.show_start
>FROM RadioShowProgramming AS P1
> INNER JOIN
> RadioShowProgramming AS P2
> ON P2.show_end <= P1.show_start
>GROUP BY P1.show_start
>HAVING MAX(P2.show_end) <> P1.show_start
>
>-- Periods of continuous show programming
>CREATE VIEW RadioShowSequences (show_start_seq, show_end_seq)
>AS
>SELECT MIN(P.show_start),
> COALESCE(P.show_start_gap, MAX(P.show_end))
>FROM (SELECT P.show_start, P.show_end,
> MIN(G.show_start_gap) AS show_start_gap
> FROM RadioShowProgramming AS P
> LEFT OUTER JOIN
> RadioShowGaps AS G
> ON P.show_end <= G.show_start_gap
> GROUP BY P.show_start, P.show_end) AS P
>GROUP BY P.show_start_gap
>
>-- The ad programming is defined as an ad's name, its start datetime, inclusive,
>-- and end datetime, exclusive. The DATETIME data type is like Standard SQL's
>-- TIMESTAMP. The integrity checks ensure that an end datetime is always
>-- after a start datetime and that ad programming doesn't overlap. There might
>-- be other business constraints to enforce, e.g., the total amount of time for
>-- ads per hour being between some pre-specified lower and upper bounds.
>CREATE TABLE RadioAdProgramming
>(
>radio_ad VARCHAR(20) NOT NULL
> REFERENCES RadioAds (radio_ad),
>ad_start DATETIME NOT NULL PRIMARY KEY, -- inclusive
>ad_end DATETIME NOT NULL UNIQUE, -- exclusive
>CHECK (ad_end > ad_start)
>/*
>This prevents ad programming from overlapping. T-SQL doesn't implement
>subqueries for constraints so this would need to be placed in a trigger.
>However, declaratively, one would have
>CHECK (NOT EXISTS
> (SELECT *
> FROM RadioAdProgramming AS P
> WHERE ((RadioAdProgramming.ad_start >= P.ad_start AND
> RadioAdProgramming.ad_start < P.ad_end) OR
> (RadioAdProgramming.ad_end > P.ad_start AND
> RadioAdProgramming.ad_end <= P.ad_end)) AND
> RadioAdProgramming.ad_start <> P.ad_start AND
> RadioAdProgramming.ad_end <> P.ad_end))
>*/
>)
>
>-- Periods of time where there's no ad programming
>CREATE VIEW RadioAdGaps (ad_start_gap, ad_end_gap)
>AS
>SELECT MAX(P2.ad_end), P1.ad_start
>FROM RadioAdProgramming AS P1
> INNER JOIN
> RadioAdProgramming AS P2
> ON P2.ad_end <= P1.ad_start
>GROUP BY P1.ad_start
>HAVING MAX(P2.ad_end) <> P1.ad_start
>
>-- Periods of continuous ad programming
>CREATE VIEW RadioAdSequences (ad_start_seq, ad_end_seq)
>AS
>SELECT MIN(P.ad_start),
> COALESCE(P.ad_start_gap, MAX(P.ad_end))
>FROM (SELECT P.ad_start, P.ad_end,
> MIN(G.ad_start_gap) AS ad_start_gap
> FROM RadioAdProgramming AS P
> LEFT OUTER JOIN
> RadioAdGaps AS G
> ON P.ad_end <= G.ad_start_gap
> GROUP BY P.ad_start, P.ad_end) AS P
>GROUP BY P.ad_start_gap
>
>-- The ads associated with shows, i.e., an ad period overlaps with a show period
>CREATE VIEW RadioShowAds (radio_show, show_start, show_end,
> radio_ad, ad_start, ad_end,
> overlap_start, overlap_end)
>AS
>SELECT SP.radio_show, SP.show_start, SP.show_end,
> AP.radio_ad, AP.ad_start, AP.ad_end,
> CASE WHEN AP.ad_start <= SP.show_start
> THEN SP.show_start
> ELSE AP.ad_start
> END,
> CASE WHEN AP.ad_end <= SP.show_end
> THEN AP.ad_end
> ELSE SP.show_end
> END
>FROM RadioShowProgramming AS SP
> INNER JOIN
> RadioAdProgramming AS AP
> ON (AP.ad_start >= SP.show_start AND
> AP.ad_start < SP.show_end) OR
> (AP.ad_end > SP.show_start AND
> AP.ad_end <= SP.show_end)
Received on Fri May 28 2004 - 07:18:52 CEST