I think you need to explain what kind of SELECTs you want to do, and what
results you expect. 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:
> 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:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
