Data Structure -- It's been a while

**-P-** · Jul 20 '05, 03:36 AM

Re: Data Structure -- It's been a while

"Jake Jessup" <watcherdude@ho tmail.com> wrote in message news:xyplc.4666 0$Qy.28399@fed1 read04...[color=blue]
> Ok, I haven't been doing too much database work lately and my brain has gone
> soft.
>
> I need some help with proper structure.
>
> My database is being used to track television shows.
>
> Any given show will be associated with at least one production company (call
> it ProdCo) but possibly up to 3 production companies (never more than that).
>
> I have:
>
> tblNetwork
> NetID (numeric, Identity)
> NetName (char, 50)
>
> tblShowData
> BookingNum (numeric, Identity)
> BookingNumExt (numeric, Identity)
>
> Now, if I was just dealing with one ProdCo I would add it tblShowData as a
> foreign key from tblNetwork; no problem.
>
> How do I structure it so that I can combine 1 to 3 ProdCo's as one reference
> in tblShowData.
>
> I tried:
>
> tblNetworkCombo
> ID (numeric, Identity)
> Net1
> Net2
> Net3
>
> The problem is, obviously I can only join the FK from tblNetworks to one of
> the NetX fields in tblNetworkCombo .
>
> I don't quite know where to go from here. Any help would be appreciated.
>[/color]

Create an associative entity that resolves the M:M relationship between shows and nets.
CREATE TABLE tblShowNetwork(
BookingNum numeric not null
REFERENCES tblShowData (BookingNum)
ON UPDATE RESTRICT
ON DELETE CASCADE,
NetID numeric not null,
REFERENCES tblNetwork (NetID)
ON UPDATE RESTRICT
ON DELETE CASCADE,
primary key( BookingNum, NetID )
;

Paul Horan
VCI Springfield, MA

We're in the TV business as well - we offer an industry-leading Sales, Traffic, and Billing system for TV/Cable
networks.

**Charles Chen** · Jul 20 '05, 03:36 AM

Re: Data Structure -- It's been a while

I think what you really want is three tables.

Two of the tables should be entity tables, and the last one should be
a relationship table:

(I use the convention "dbe_" for database entity and "dbr_" for
database relation).

For the sake of simplicity, I have not checked the code to ensure it
works:

---------------------------------------------------------
CREATE TABLE dbe_productionC ompanies(
pcId int,
pcName varchar(64)
)

CREATE TABLE dbe_shows(
showId int,
showName varchar(64)
)

CREATE TABLE dbr_showProduct ionCompanies(
showId int,
pcId int,
CONSTRAINT FK_showProducti onCompanies_REF _productionComp anies
FOREIGN KEY (pcId)
REFERENCES dbe_productionC ompanies(pcId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_showProducti onCompanies_REF _shows
FOREIGN KEY (showId)
REFERENCES dbe_shows(showI d)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT PK_showProducti onCompanies
PRIMARY KEY (showId, pcId)
)
---------------------------------------------------------

To select the names of the production companies for a given show, you
would use the following query:

SELECT pcs.pcName
FROM dbr_showProduct ionCompanies AS spc
JOIN dbe_productionC ompanies AS pcs ON spc.pcId = pcs.pcId
WHERE spc.showId = <<<ANY_ID>>>

This should return a resultset with up to 3 matches and no more. Each
of the matches should be unique since there is a primary key
constraint on the two fields so that no duplicate fields are entered.

What you are really specifying is a one-to-many many relationship
between show and production companies. Being that it is one to many,
the most efficient way to manage the relationship (unless the join is
large) is to break it up into the entities and a relationship table
(at least this is what I have been taught).

Play around with some of your sample data to see how well those
foreign key dependencies work for you; you may not want the cascading
behavior.

Good luck.

**Jake Jessup** · Jul 20 '05, 03:37 AM

Re: Data Structure -- It's been a while

I got it figured out. You helped out a lot. Thanks!

"Charles Chen" <c.chen@charlie digital.com> wrote in message
news:56f97b20.0 405030930.41f52 1c0@posting.goo gle.com...[color=blue]
> I think what you really want is three tables.
>
> Two of the tables should be entity tables, and the last one should be
> a relationship table:
>
> (I use the convention "dbe_" for database entity and "dbr_" for
> database relation).
>
> For the sake of simplicity, I have not checked the code to ensure it
> works:
>
> ---------------------------------------------------------
> CREATE TABLE dbe_productionC ompanies(
> pcId int,
> pcName varchar(64)
> )
>
> CREATE TABLE dbe_shows(
> showId int,
> showName varchar(64)
> )
>
> CREATE TABLE dbr_showProduct ionCompanies(
> showId int,
> pcId int,
> CONSTRAINT FK_showProducti onCompanies_REF _productionComp anies
> FOREIGN KEY (pcId)
> REFERENCES dbe_productionC ompanies(pcId)
> ON UPDATE NO ACTION
> ON DELETE CASCADE,
> CONSTRAINT FK_showProducti onCompanies_REF _shows
> FOREIGN KEY (showId)
> REFERENCES dbe_shows(showI d)
> ON UPDATE NO ACTION
> ON DELETE CASCADE,
> CONSTRAINT PK_showProducti onCompanies
> PRIMARY KEY (showId, pcId)
> )
> ---------------------------------------------------------
>
> To select the names of the production companies for a given show, you
> would use the following query:
>
> SELECT pcs.pcName
> FROM dbr_showProduct ionCompanies AS spc
> JOIN dbe_productionC ompanies AS pcs ON spc.pcId = pcs.pcId
> WHERE spc.showId = <<<ANY_ID>>>
>
> This should return a resultset with up to 3 matches and no more. Each
> of the matches should be unique since there is a primary key
> constraint on the two fields so that no duplicate fields are entered.
>
> What you are really specifying is a one-to-many many relationship
> between show and production companies. Being that it is one to many,
> the most efficient way to manage the relationship (unless the join is
> large) is to break it up into the entities and a relationship table
> (at least this is what I have been taught).
>
> Play around with some of your sample data to see how well those
> foreign key dependencies work for you; you may not want the cascading
> behavior.
>
> Good luck.[/color]

Data Structure -- It's been a while

Data Structure -- It's been a while

Comment

Comment

Comment