Subject Re: [firebird-support] Many to many (tables) relationship
Author Michael Ludwig
Rick Debay schrieb am 27.05.2010 um 11:38:10 (-0400):
> Currently each result has foreign keys that point to the inputs and
> the parameters for the algorithm that processed the inputs.
> Results table
> -------------
> ID,R1,Rn,FK_to_inputs,FK_to_parameters
> Parameter table
> ---------------
> ID,P1,Pn
> Now I need to be able to have more than one parameter table. Each
> result needs to be associated with only one of the many tables.

Associated with only one of many tables.

> Results table
> -------------
> ID,R1,Rn,FK_to_inputs,FK_to_parameters1, FK_to_parameters2...

Here, you're providing for associations to multiple tables. Or NULLs.

> I'm thinking this may be what I'll end up with, and once the third
> table gets added then I'll have to reexamine the whole data model.

Alternative proposition, go without a foreign key, use a column to
identify the table and another to identify the row, and have custom
triggers ensure data integrity by means of EXISTS predicates:

ID,R1,Rn,FK_to_inputs, param_table_id, row_id_but_not_FK

+ custom trigger watching over param_table_id and row_id_but_not_FK

> The drawback, besides it being a kludge, is that in order to require
> that a relationship exists I'll need to use a trigger instead of just
> requiring the FK to be not-null.

Use custom triggers, but not the FK, which is too rigid for your data

Of course, this means that every JOIN will have to take param_table_id
into account: you can only join against one parameter table at a time.
But you can use UNION ALL to reunite the results for both or all

Michael Ludwig