:) Richard
On Thu, Sep 13, 2012 at 12:15 PM, MichaelF <mjfs...@gmail.com> wrote: > 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<http://pragprog.com/book/bksqla/sql-antipatterns> >> >> Richard >> >> >> On Thu, Sep 13, 2012 at 11:40 AM, MichaelF <mjf...@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. >>>>> >>>>> -- >>>>> >>>>> >>>>> >>>>> >>>> >>>> -- >>> >>> >>> >>> >> >> -- > > > > --
