Data Structure -- It's been a while

[Jake Jessup]

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.

[-P-]

"Jake Jessup" <wa*********@hotmail.com> wrote in message news:xyplc.46660$Qy.28399@fed1read04...

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.

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]

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_productionCompanies(
pcId int,
pcName varchar(64)
)

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

CREATE TABLE dbr_showProductionCompanies(
showId int,
pcId int,
CONSTRAINT FK_showProductionCompanies_REF_productionCompanies
FOREIGN KEY (pcId)
REFERENCES dbe_productionCompanies(pcId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_showProductionCompanies_REF_shows
FOREIGN KEY (showId)
REFERENCES dbe_shows(showId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT PK_showProductionCompanies
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_showProductionCompanies AS spc
JOIN dbe_productionCompanies 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]

I got it figured out. You helped out a lot. Thanks!
"Charles Chen" <c.****@charliedigital.com> wrote in message
news:56**************************@posting.google.c om...

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_productionCompanies(
pcId int,
pcName varchar(64)
)

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

CREATE TABLE dbr_showProductionCompanies(
showId int,
pcId int,
CONSTRAINT FK_showProductionCompanies_REF_productionCompanies
FOREIGN KEY (pcId)
REFERENCES dbe_productionCompanies(pcId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_showProductionCompanies_REF_shows
FOREIGN KEY (showId)
REFERENCES dbe_shows(showId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT PK_showProductionCompanies
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_showProductionCompanies AS spc
JOIN dbe_productionCompanies 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.