My answers interspersed below (and yes, I have read his follow up reply
that had additional information)
Giulio <[EMAIL PROTECTED]> wrote on 10/11/2004 05:44:43 AM:
> 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.
I would think that this structure (#2) would fit your model the best. Each
Table A element could contain various elements of tables B and C depending
on the schedule/format of that show. Table D would be your "logging" table
that would relate elements of Table A to elements of Tables B or C along
with a time stamp (based on what time in the program the element
appeared.) I have seen many databases designed with a table like D that
needed to contain references to objects of different types.
I would create tableD to look something like this:
CREATE TABLE content_log (
ID int auto_increment primary key,
tableA_id int not null,
Object_ID int not null,
Object_type (here you have options, you could use a SET, ENUM,
INT, or CHAR datatype),
TimeOffset time not null
)
>
> 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
So.. if you wanted to find all of the movies shown during Show # 14
SELECT *
FROM content_log
WHERE tableA_ID = 14
and object_type='movie' /*or however you set up that column*/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
