Thanks. Yes, option 1 is the 'approved' solution, I think. It doesn't get tricky.
And thanks for the link. I like anti-pattern books...great sanity checks. Unfortunately, I find a lot of my code in them! :) On Thursday, September 13, 2012 9:45:38 AM UTC-6, Richard wrote: > > 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 <mjf...@gmail.com <javascript:> > > 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. >>>> >>>> -- >>>> >>>> >>>> >>>> >>> >>> -- >> >> >> >> > > --
