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