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

modeling.

I was afraid that might be the case. I think I'll have to refactor when
I get the time.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Michael Ludwig
Sent: Thursday, May 27, 2010 5:33 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Many to many (tables) relationship

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

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

--
Michael Ludwig


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.