In this case the best approach will be to have a normalized schema that allow to use all the constraint required at the DB level... I think yo said the first proposal you made allow that...
I like this book when it comes to tricky design, it gives ideas on how to solve the issues : http://pragprog.com/book/bksqla/sql-antipatterns Richard On Thu, Sep 13, 2012 at 11:40 AM, MichaelF <mjfs...@gmail.com> wrote: > 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> 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. >>> >>> -- >>> >>> >>> >>> >> >> -- > > > > --
