On Apr 11, 3:47=A0pm, FastWolf <wolfsof...@[EMAIL PROTECTED]
> wrote:
> On Fri, 11 Apr 2008 11:13:33 -0700 (PDT), Diego B <messa...@[EMAIL PROTECTED]
>
> wrote:
> Diego,
Hi Fastwolf, first of all thank you for the help
>
> What you describe suggests that the auto-created records in the
> related tables need to be deleted when the main record is deleted. You
> could script that easily, since as you say all the related records
> have identical serials. =A0
----SNIP----
Absolutely, and indeed I created that script very easily.
However, I did not clarify my situation enough:
The DB is organazed with a Main table (demographics)
using ID as primary key, unique and auto-incrementing.
There are also 9 other tables re****ting different clinical
examinations, and
using ID as foreign key (every table has also an independent primary
key
that is invisible and i do not care much of).
This ID is not simply there for structural purposes, but
at the same time it gives me an immediate count of how many patients
are in the DB.
For this, I really would like to have missing values in the ID count
(such as
99, 100, 101, 102, 104, 105, 107...) but the consistency should be
maintained accross all the tables also when I delete a record,
so that if I delete record 101, the next new record should have an ID
of 101 and not 102.
Since all the tables are related through the ID, and there no many to
many relations,
I would not create join tables if possible.
I was thinking to solve in the following way:
1) Main Table: Create ID (priumary key) as a script (or a calculation)
that compute correctly the last ID inserted and
add number of the new record consistently (if I delete last record Nb
101, then the new record should have ID =3D 101
and not 102, as it is now)
2) Related Tables: creating a new record, ID (foreign key) in this
table should have the same value present in the Main table
(a sort of looked up value).
In this way chances of errors or inconsistencies should be minimized,
but I do not know exactly how to implement
this in FM 9...any suggestions ?
Diego
|
