Re: OT: SQL & Dave
- From: Keith Rose <xkeithx@xxxxxxxxxxxxx>
- Date: Mon, 23 Mar 2009 18:43:25 GMT
The short answer to your question is yes, you can use the show#
"like" a foreign key to join rows from the Guest_Table with the
Basic_Data table.
Let's say you wanted the names of all red-haired female guests:
SELECT g.name, b.date_broadcast
FROM Guest_Table g, Basic_Data b
WHERE g.show_number = b.show_number AND
g.hair_color = 'red' AND
g.gender = 'F';
My question, however, is: Why can't you use the show number as your
primary key? You still have "show numbers" for anomaly shows, correct?
As for the date fields, I suggest one "Broadcast_Date" table, with these
fields:
Show_Number
Broadcast_Date
Broadcast_Channel [NBC, A&E, E!, Trio, etc.]
Broadcast_Type [Original, Rerun]
Then you could trim most of the date fields (except for date_taped) from
your Basic_Data table, but still join the data together when needed.
Keith [just catching up...]
In article <d7775c23-0716-4a64-b33b-5e8e1221f2d6@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
Donz5 <donz5@xxxxxxx> wrote:
Anyone knowledgeable enough about SQL (or MySQL) to help a poor soul
who's starting to design a supersecretinformationaldatabase that'll
eventually get online?
Here's the dilemma:
One of the tables will consist of "basic show data" (for LN only for
now):
Fields:
show number
day of week
date taped
date broadcast
date rerun 1
date rerun 2
date rerun 3
A&E broadcast
E! broadcast 1
E! broadcast 2
Trio broadcast (initial only)
My initial thought was to have the "show number" be the primary key,
so that it'll be the foreign key in other tables (guests, staff,
comedy, etc.).
But I'm reading that's not a great idea, partly because of anomalies
in the "show number" field: there are 5 shakedown shows before LNwDL
#1 (plus a 6th in 1988), and there's the unnumbered First Film
Festival special from 1985.
So the solution would be to create a separate Primary Key, with the
"show number" just another field that corresponds to the Primary Key.
Problem with that -- I'm reading -- is that the foreign key in other
tables would have to refer to the primary key in this table, which
won't correspond with the actual show number.
(Example: Primary Key 0001 would refer to Shakedown Show #1, PK 0002
to SS #2, etc., while Primary Key 0006 would refer to LNwDL Show #1.
Thus, say, in the Guest table, Bill Murray would be attached to
Primary Key 0006, and so I'd have to adjust everything in Show #1 to
0006 instead of the actual show number. And adjust every other show as
well.)
So here's the question: could I set up those other tables (guest,
staff, performance, comedy, etc.) so that I could reference the "show
number" as the foreign key? I'm getting conflicting (or confusing)
answers online -- various site (and books I have) declare a foreign
key in one table must refer to the primary key in another table --
while other sites mention foreign keys referring to a "unique" key in
another table, which might not necessarily be the primary key.
If the latter, then I'm hoping I could do just that -- have the
foreign key in, say, the Guest table (Bill Murray) refer to the "show
number" field in the "basic show data" table. That way, Murray's
"foreign key" number would be 0001 (the show number) rather than 0006
(the primary key number).
(The shakedown shows would be ID'd as SS1, SS2, etc.; the Film
Festival show from 1985 could be ID'd as FF1 -- this would require
other things -- like making sure the "show number" field wasn't
limited to integers only.)
Or am I stuck with the PK rule (where LNwDL #1 = PK 0006)?
If anyone's still awake (::cough:: Helen ::cough::), I'd welcome your
thoughts.
Thanks!
.
- Follow-Ups:
- Re: OT: SQL & Dave
- From: Donz5
- Re: OT: SQL & Dave
- References:
- OT: SQL & Dave
- From: Donz5
- OT: SQL & Dave
- Prev by Date: Re: Dave's Bear Story
- Next by Date: Re: OT: SQL & Dave
- Previous by thread: Re: OT: SQL & Dave
- Next by thread: Re: OT: SQL & Dave
- Index(es):
Relevant Pages
|
