Re: Data structures for time scheduling

From: John Gilson <jag_at_acm.org>
Date: Mon, 24 May 2004 21:46:08 GMT
Message-ID: <A2usc.140664$WA4.111559_at_twister.nyc.rr.com>


"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)
--
JAG
Received on Mon May 24 2004 - 23:46:08 CEST

Original text of this message