| Subject | Re: [firebird-support] Re: Foreign Key on 2 or more columns |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-09-25T08:04:58Z |
At 05:03 PM 25/09/2009, you wrote:
It seems to me you need TableA to be the master of the relationship, with the field A.ID_contact being the reference target for identical FKs in both ContactA and ContactB. Are you with me here?
Then, you can archive records from ContactA into ContactB whenever you like; and it won't cause any grief if you have some TableA records that have no contact assigned. As long as there is a match for the referencing column in *either* ContactA or ContactB, the FK relationship won't be violated.
If it is a little more complex than a simple 1:1 relationship - the same contact_ID can exist in multiple TableA records and thus it is not possible to put a UNIQUE constraint on TableA.ID_contact - then you will need an intersection table to manage the many:many relationship, instead of having the REFERENCES referencing the TableA record directly.
If you are changing contact IDs in TableA the many:many model will almost certainly be needed. This is still classic relational database stuff, though, no magic needed, just steady hands and only still water with your luncheon. ;-)
As for archiving contact records, you will have to figure out some criteria for selecting the records to be copied over to ContactB from ContactA. You might like to think about a simple smallint ARCHIVE_STATUS flag with varying values related to archiving, e.g., null means "never archived", 1 means "ready to archive", 2 means "ready to delete". I would recommend a 3-step process: decide to archive and set 1; perform the archiving on records with flag 1 and set flag 2; then, afterwards, delete records from ContactA that have flag 2.
It is not a bad idea to plan to do it with a stored procedure: a master procedure, that calls sub-procedures to perform the steps.
./heLen
>not contact, was an exemple... just real estate properties. I don't like the idea that the soft must control the FK because one day one developper will do a bulsheet and the database could be corrupted. i prefere a way to control the pseudo-fk by trigger inside the database engine. but in fact worse is that the table archived could also be in a separate database in this way... :(Going right back to the start of this thread, you said:
>in tableA i have some record with the field ID_contact that point to the table ContactA or to the table ContactB. so in this way i can not create a foreign key on my field ID_Contact.Why are you trying to design it so that the contact tables are the master[s] of the relationship?
It seems to me you need TableA to be the master of the relationship, with the field A.ID_contact being the reference target for identical FKs in both ContactA and ContactB. Are you with me here?
Then, you can archive records from ContactA into ContactB whenever you like; and it won't cause any grief if you have some TableA records that have no contact assigned. As long as there is a match for the referencing column in *either* ContactA or ContactB, the FK relationship won't be violated.
If it is a little more complex than a simple 1:1 relationship - the same contact_ID can exist in multiple TableA records and thus it is not possible to put a UNIQUE constraint on TableA.ID_contact - then you will need an intersection table to manage the many:many relationship, instead of having the REFERENCES referencing the TableA record directly.
If you are changing contact IDs in TableA the many:many model will almost certainly be needed. This is still classic relational database stuff, though, no magic needed, just steady hands and only still water with your luncheon. ;-)
As for archiving contact records, you will have to figure out some criteria for selecting the records to be copied over to ContactB from ContactA. You might like to think about a simple smallint ARCHIVE_STATUS flag with varying values related to archiving, e.g., null means "never archived", 1 means "ready to archive", 2 means "ready to delete". I would recommend a 3-step process: decide to archive and set 1; perform the archiving on records with flag 1 and set flag 2; then, afterwards, delete records from ContactA that have flag 2.
It is not a bad idea to plan to do it with a stored procedure: a master procedure, that calls sub-procedures to perform the steps.
./heLen