SpecificTableA : SpecificTableB : SpecificTableC
linktable(s)->>
GenericTableA : GenericTableB : GenericTableC
Lets say an single entry(row) in GenericTableA could be linked to 5 rows in SpecificTableA, 3 in SpecificTableB and 4 in SpecificTableC
and likewise for for GenericTableB and C
Conversely a single entry(row) in SpecificTableA could be linked to 5 rows in GenericTableA, 3 in GenericTableB and 4 in GenericTableC
and likewise for for SpecificTableB and C
This obviously needs link tables to tie the data together, however the best format for these links tables would be..?
a:
a link table for every combination.. kind of weighty
linktable_specA_genA
linktable_specA_genB
linktable_specA_genC
linktable_specB_genA (and so on)
table cols: [link_id, spec_rowid, gen_rowid]
b:
a link table for every generic table:
linktable_genA
linktable_genB
linktable_genC
table cols: [link_id, spec_tableid, spec_rowid, gen_rowid]
c:
a single link table for everything
linktable
table cols: [link_id, spec_tableid, spec_rowid, gen_tableid, gen_rowid]
or (d)?? any more ideas?
keep in mind that there could be 10 specific tables, and 20+ generic tables, any table could get upwards of 2 million rows
ideas people?

