The P3 record will have text and/or file information that relates to
several P1 records, or several P2 records, and sometimes both several P1
and several P2 records. The text info will be used to add to a document (a
totally separate entity outside the db), and the file will be attached to
the document. Also, the linking record will have start/end date fields that
specify the valid dates for the relationship.
So, a single P3 record (let's call it P3.1) might be associated with P1.4
from 1/1/12 through 12/31/12; and also be associated with P1.6 from 6/1/12
through 6/2/12; and also be associated with P2.6 record from 11/1/12
through 11/11/12. That would be three separate linking records (regardless
of which option we used): P1.4 => P3.1; P1.6 => P3.1, and P2.6 => P3.1.
On Thursday, September 13, 2012 9:11:14 AM UTC-6, Richard wrote:
>
> Maybe with more details about the nature of the information to store, it
> could be easier to give an answer...
>
> You can also use the junction table to store weak entity attribute, that
> could avoid the P3 table.
>
> Richard
>
> On Thu, Sep 13, 2012 at 11:01 AM, MichaelF <mjf...@gmail.com <javascript:>
> > wrote:
>
>> This might be more of a SQL design question, but if web2py handles one
>> better than another, that would be good to know.
>>
>> Suppose I have three 'parent' records ((P1, P2, and P3), and I want to
>> link P1 records with P3 records, and also P2 records with P3 records.
>> Several options:
>>
>> Option 1: obvious: one linking table per relationship
>> define_table('P1_P3_linker',
>> Field('P1', db.P1), Field('P3', db.P3))
>> define_table('P2_P3_linker',
>> Field('P2', db.P2), Field('P3', db.P3))
>>
>> Option 2: one linking table for all relationships; each record still
>> links one record (P1 or P2) with one P3 record
>> define_table('P1_P2_P3',
>> Field('P1', db.P1),
>> Field('P2', db.P2),
>> Field('P3', db.P3))
>>
>> Option 3: overload linking field and use a 'type'; each record still
>> links one record (P1 or P2) with one P3 record
>> define_table('P1_P2_P3',
>> Field('Table_type', 'string', IS_IN_SET(['P1', 'P2'])),
>> Field('Table_key', 'integer'), # will be P1.id or P2.id
>> Field('P3', db.P3))
>>
>> Using Option 2 I would relate a P1 to a P3 by populating the P1_P2_P3.P1
>> and P1_P2_P3.P3 fields, setting P1_P2_P3.P2 to NULL. I would relate a P2 to
>> a P3 by populating the P1_P2_P3.P2 and P1_P2_P3.P3 fields,
>> setting P1_P2_P3.P1 to NULL. This assumes the underlying db allows null
>> fields for foreign keys.
>>
>> Using Option 3 I would relate a P1 to a P3 by setting Table_type to 'P1',
>> then setting P1_P2_P3.Table_key to P1.id. No constraints can be set on this
>> table using this option. This also assumes that the key field of the
>> underlying db is integer.
>>
>> If it were just two tables (P1 and P2) relating to P3 then Option 1 makes
>> sense. I actually have P1 through P5 relating to P6. I suspect, though,
>> that Option 1 is still the best, and that the others are "penny wise, pound
>> foolish" in trying to avoid defining the additional linking tables.
>>
>> Thoughts? Thanks.
>>
>> --
>>
>>
>>
>>
>
>
--
