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. --