Il giorno 11/ott/04, alle 11:53, [EMAIL PROTECTED] ha scritto:
I think you need to explain what kind of SELECTs you want to do, and what
results you expect.
you're right, I'll try to explain it better
I'm working on a system that must keep track of all the music broadcasted by a tv,
so, let's call record A TVprogram, TVprogram table will contain all the programs broadcasted in cronological order, they have a broadcasting date, start time and end time, type of program ( news, entertainment, cartoons, and so on... )
every TVprogram record can have one or more associated records of two types:
a record B ( let's call it MusicTrack ), containg info ( title, composer, etc. ) about a Music track used on some way in the TV program
a record C ( let's call it Movie ), containing info ( title, director, etc. ) about a movie or serial or cartoon and so on broadcasted during the TV program. ( the list of music tracks used on the movie will be extracted from another database at later time).
MusicTrack and Movie are associated to a given TVprogram on a progressive ( and cronological ) order.
So I.E., for a TVprogram record I could have:
1 a MusicTrack record with info about a song used as intro for the program
2 a MusicTrack record with info about a song used as background music while talking about the movie that will be broadcasted
3 a Movie record containing info about the movie itself
4 a MusicTrack record with info about a song used at the end of the program
the select I would like to perform is, given a TVprogram element, have a list of all its MusicTrack or Movie records in crological order, or have a list of TVprogram elements on a given interval, and for everyone of them a list of their referred records.
but you're right, I now think the possible solutions are to merge the two table type on one table type, or keep them separated, perform two different separate joins and then merge them by code...
thank you,
Giulio
How do you expect to get results from a SELECT which
returns hits in both the B and C tables? If you expect to do this, then
the D table is probably your correct answer. Do you really need a rec_type
field? Can you not leave the columns which exist only in B type records
null in c-type records and vice versa? How much commonality is there
between B and C type fields? I presume there is some, or you would not be
wanting to merge them.
Incidentally, I think you only need a simple join, not a left join - unless I misunderstand.
Alec
Giulio <[EMAIL PROTECTED]> wrote on 11/10/2004 10:44:43:
http://lists.mysql.com/[EMAIL PROTECTED]Hi all,
I have some doubts about how to implement this kind of scenario:
I have a table of elements in cronological order, let's call it table A:
for every A element, I have a number of elements ordered on a
progressive number.
This could be a simply one-to-many relation, where I can handle a list
of all A records with related B records using a left join.
the issue is complicated ( for me, at least ) by the fact that the
records related to table A can be of two different types, that have in
common some fields but not others. I mean for every record A I have an
ordered list of mixed records B and C.
So I'm thinking about pro and cons of three different ways to handle this problem.
1) create tables A,B, and C, with tables B and C having a field id_A
containing the ID of records A they belong, and figure out how to
handle a left join having oh its right side elements from two different
tables
2) create tables A,B, and C, and create an intermediate table D to link
table A elements with their related B and C elements, and again figure
out how to handle the list of A elements with linked B and C elements.
3) create only tables A and D, where table D is a mix of the fields
from tables B and C with added a fileld rec_type to handle different
fields depending on the record type ( this seems to me to be the
simplest solution, although not the best in term of normalization rules
)
Hope it was all clear,
thanx in advance,
Giulio
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
