serial numbers as primary key issue: how would you solve ?

Dear group,

As usual I am here with some questions to improve my clinical reserach
DB running on FM 9 advanced Server (Win 2003) and FM 9 advanced (Win
XP) on 10 clients.

At this time, the DB use an autoenter auto-incrementing serial number
as primary key.
Every time I create a new record, a new number is created on 10
different tables so that they can relate together. This works pretty
well, except when I have to delete a record (usually the last one). In
that case I have to re-adjust manually the serial number in all the
tables, to avoid that some serials will be lost or a different
counting among the tables.

This means also that no one but me can insert the new patient in the
database, to avoid confusions and problems. As you see this is
completely unacceptable.

Looking on-line I have found a discussion on a public FM mailing list
(Filemaker Talk run by Yahoo, I guess) presenting the same problem.
Here I report the solutions suggested, to discuss what is the best in
your opinion and what solution you would adopt:

I Method

For my opinion the best way to solve such a problem is by use a
Number field with an auto-enter calculation as follows:

If ( IsEmpty ( Self_Prefs:: Rec_Nr ) ; 1 ; Max( Self_Prefs:: Rec_Nr )
+ 1)

It means that you must create a self relation to the table (or file),
the one I call Self_Prefs. That relationship is based on a number
field which has always the same number, so as any reference to it
will display the whole contents of the table. Without this
relationship if you isolate one record and create a new record, that
new record will take the number next to the one you isolate (find),
if your table or file name is Prefs.
As you see the only calculation you really need is,

Max( Self_Prefs:: Rec_Nr ) + 1),

but what happens if the table or file is empty? The relationship will
not work. Thats the case of the "if" function, it gives an initial
number so to begin the process. If delete any record, and not the
last record only, the next new record, regardless if it is in a found
set or or all the records, will take the correct number.
No Scripts
No Buttons.


II Method

>
> > I would recommend using an auto enter calculation instead of the
> > serial number. You can then do a simple formula:
> >
> > Get(TotalRecordCoun t)+1



III Method


> > > Simple approach to serial number issue:
> > >
> > > Setup a separate table with a field to track serial numbers. Then
> > each time a new serial
> > > number is needed, check the current number in the serial number
> > table and increment by
> > > one. Save the new number to the serial number table, and use it
> > for your product tracking.
> > > The serial number table will always contain one field unless you
> > want to track other
> > > numbers, date of last serial#, etc.


I hope there is nothing bad in quoting as above, since the list and
the mails are public.

Thank you as always for any advice,

Diego

Re: serial numbers as primary key issue: how would you solve ?

On Fri, 11 Apr 2008 11:13:33 -0700 (PDT), Diego B
wrote:

>Dear group,
>
>As usual I am here with some questions to improve my clinical reserach
>DB running on FM 9 advanced Server (Win 2003) and FM 9 advanced (Win
>XP) on 10 clients.
>
>At this time, the DB use an autoenter auto-incrementing serial number
>as primary key.
>Every time I create a new record, a new number is created on 10
>different tables so that they can relate together. This works pretty
>well, except when I have to delete a record (usually the last one). In
>that case I have to re-adjust manually the serial number in all the
>tables, to avoid that some serials will be lost or a different
>counting among the tables.
>
>This means also that no one but me can insert the new patient in the
>database, to avoid confusions and problems. As you see this is
>completely unacceptable.
>
>Looking on-line I have found a discussion on a public FM mailing list
>(Filemaker Talk run by Yahoo, I guess) presenting the same problem.
>Here I report the solutions suggested, to discuss what is the best in
>your opinion and what solution you would adopt:
>
>I Method
>
>For my opinion the best way to solve such a problem is by use a
>Number field with an auto-enter calculation as follows:
>
>If ( IsEmpty ( Self_Prefs:: Rec_Nr ) ; 1 ; Max( Self_Prefs:: Rec_Nr )
>+ 1)
>
>It means that you must create a self relation to the table (or file),
>the one I call Self_Prefs. That relationship is based on a number
>field which has always the same number, so as any reference to it
>will display the whole contents of the table. Without this
>relationship if you isolate one record and create a new record, that
>new record will take the number next to the one you isolate (find),
>if your table or file name is Prefs.
>As you see the only calculation you really need is,
>
>Max( Self_Prefs:: Rec_Nr ) + 1),
>
>but what happens if the table or file is empty? The relationship will
>not work. Thats the case of the "if" function, it gives an initial
>number so to begin the process. If delete any record, and not the
>last record only, the next new record, regardless if it is in a found
>set or or all the records, will take the correct number.
>No Scripts
>No Buttons.
>
>
>II Method
>
>>
>> > I would recommend using an auto enter calculation instead of the
>> > serial number. You can then do a simple formula:
>> >
>> > Get(TotalRecordCoun t)+1
>
>
>
>III Method
>
>
>> > > Simple approach to serial number issue:
>> > >
>> > > Setup a separate table with a field to track serial numbers. Then
>> > each time a new serial
>> > > number is needed, check the current number in the serial number
>> > table and increment by
>> > > one. Save the new number to the serial number table, and use it
>> > for your product tracking.
>> > > The serial number table will always contain one field unless you
>> > want to track other
>> > > numbers, date of last serial#, etc.
>
>
>I hope there is nothing bad in quoting as above, since the list and
>the mails are public.
>
>Thank you as always for any advice,
>
>Diego

Diego,

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. But I really don't see why the related
records in the related tables need to have serials identical to the
related record in the main table. What you might do instead is create
a set of join tables to store data on related records. Put a join
table between the main table and each related table. All records in
the tables can have their own serials, it doesn't matter what they are
(as long as they're unique), those values are stored in the join
table.

For example:

-- -- -- - -- -- -- - -- -- -- -
MAIN TABLE JOIN TABLE RELATED TABLE
m_serial <--> m_serial= X
value= X r_serial= A <--> r_serial
value= A

The fields m_serial and r_serial are keys. In Define Relationships,
you create a relationship between MAIN TABLE and JOIN TABLE on
m_serial, and between JOIN TABLE and RELATED TABLE on r_serial. Then,
to create connected sets of records, just create a new record in JOIN
TABLE containing the m_serial and r_serial of the records you want to
relate. You can add a j_serial to JOIN TABLE if you want to be able
to track the joins by giving each its own unique key.

hope this helps

--
FW

Re: serial numbers as primary key issue: how would you solve ?

On Apr 11, 3:47=A0pm, FastWolf wrote:
> On Fri, 11 Apr 2008 11:13:33 -0700 (PDT), Diego B
> 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 reporting 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

Re: Re: serial numbers as primary key issue: how would you solve ?

On Fri, 11 Apr 2008 16:06:28 -0700 (PDT), Diego B
wrote:

>On Apr 11, 3:47 pm, FastWolf wrote:
>> On Fri, 11 Apr 2008 11:13:33 -0700 (PDT), Diego B
>> 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.  
>
>----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 reporting 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 ...

In my opinion this plan is just asking for problems. Short of doing
what you're trying to avoid doing (i.e. manually maintaining a record
of which ID[s] were deleted, are available, is next, etc) there's
really no simple way (or indeed any way) to automate this.

>... so that if I delete record 101, the next new record should have an ID
>of 101 and not 102.

Once a db auto-assigns a unique key value, it's a bad idea to use that
key value again. Let the system auto-assign the way it's designed to
do, there are good reasons for that.

>I would not create join tables if possible.

Join tables are actually perfect for you, because when you want to
delete the related records you just delete the records from the join
table. The main tables don't have to be changed all that much, so
there's less chance of error or accidental data loss in the tables
that, after all, contain your core data. Using join tables would also
facilitate your stated goal of making the db more accessible to the
others on your crew -- without the risk of giving them access to the
core data except as absolutely necessary, and then only through a set
of scripts with appropriate permissions.

>I was thinking to solve in the following way:

[snip]

I can't really speak to whether your proposal would work or not.

>...any suggestions ?

See the foregoing. :) My impression is, the main reason you want to
preserve this method of assigning key values to records is to
facilitate at-a-glance counting of the number of records. Have I got
that right? Because that's something you could do very easily with a
summary field. Create a summary field that counts the total number of
records with data in the primary key field. If the db is set to
always generate a unique key on creating/committing a record, with
validation (must be unique, null value not allowed), then a summary
field can count the number or records by primary key and show it to
you at-a-glance anywhere you like.

hope this helps

--
FW

FileMaker Pro Advanced on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2

Re: serial numbers as primary key issue: how would you solve ?

On 2008-04-11 11:13:33 -0700, Diego B said:

> Every time I create a new record, a new number is created on 10
> different tables so that they can relate together. This works pretty
> well, except when I have to delete a record (usually the last one). In
> that case I have to re-adjust manually the serial number in all the
> tables, to avoid that some serials will be lost or a different
> counting among the tables.
>
> This means also that no one but me can insert the new patient in the
> database, to avoid confusions and problems. As you see this is
> completely unacceptable.
>
> Looking on-line I have found a discussion on a public FM mailing list
> (Filemaker Talk run by Yahoo, I guess) presenting the same problem.
> Here I report the solutions suggested, to discuss what is the best in
> your opinion and what solution you would adopt:

There is a fourth method, and one I have adopted:

Do not worry about gaps in serial order in keys. This only causes
problems such as you have encountered. In additon, you get messes when
users have 100 records, and want to delete record 72. What happens
then? Does 72 get re-used? If patient 72 dies, does everyone have to
learn that the new patient 72 is different from the old one? Real world
problems caused by illogical dependence on a false premise.

In addition, you're probably violating one of the primary rules of
building proper keys by imbuing meaning in your key fields. Most
likely, the users insist on filled serial sequences as some sort of
"count" of patients. As developers, we know that counts come from found
sets and summary fields, not the last number in a serial field. Users
have trouble understanding that. It's up to us to explain it to them.
Other clients I have had have needed strict sequences, for purchase
orders, for instance, to insure that no forms have gone missing to be
used improperly by personnel.

If users insist on pristine serial order, use hidden keys for relater
key fields, and use the serials only for visible reference by the
users. Hidden keys are generated by appending a serial (yes, they are
the best way to guarantee uniqueness in FM) to a string created by
transforming the creation timestamp into a number string. This way I
never have to revise the serial when upgrading a file. If the serial
repeats it's no big deal, because the timestamp never will. For an even
more elegant solution, see the example file at
http://www.nightwing.com.au/FileMaker/demos9/demo910.html from Ray
Cologon.

Then, for the field VISIBLE to the user, generate your sequential
serial numbers from a table. When a set of records is deleted, say old
Number 72, you can recreate or unmark the #72 record in the number
assignment table, and hand it out the next time you need to assign a
number. You simply script the assignment of the first available number
in that table. Look into record-locking issues, though, so that two
users who hit the table at the same time don't both grab the same
record/number.

As you can guess, this approach requires complete scripting of every
creation or deletion of a record. For that, you'll have to use Custom
Menus to capture every Command-N or Command-E and run them through a
script.

As for trying the "Max" or calculated approach, most of them are
lacking when the missing number isn't the last one in sequence.

Lynn Allen
www.semiotics.com

Re: serial numbers as primary key issue: how would you solve ?

On Apr 11, 5:06 pm, Diego B wrote:
> On Apr 11, 3:47 pm, FastWolf wrote:
>
>
> 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.
>

Wrong tool for the job. How are you accessing this number? I can't
think of a way that would be easier than a script that goes:

Show All Records
Set Field[globalCount; Get(FoundCount)]

Put globalCount on your layout and with a button to call it and you're
good to go.

Re: serial numbers as primary key issue: how would you solve ?

> Wrong tool for the job. =A0How are you accessing this number? =A0I can't
> think of a way that would be easier than a script that goes:
>
> Show All Records
> Set Field[globalCount; Get(FoundCount)]
>
> Put globalCount on your layout and with a button to call it and you're
> good to go.

Yes, now understand how much wrong I was and how better I feel now
that I fixed the problem (Thanks in particular to Lynn for the great
insight).
I replaced my old, weak serial with a much stronger auto-enter and
auto-increment
ID that is the combination of

1) The IP address of the computer from which the new patient is
inserted

2) A traditional auto-incrementing serial number

3) Timestamp of the record creation.

ID is now invisible to anybody and I learned to not be upset for the
gaps.

I will implement summaries and reports to have a clearer idea of the
database content.

Last consideration: the journey from user to developer is very though
sometimes, and I recorgnize
to be at the very beginning of that.

Thank you again to everybody for the help anmd explanations : )

Diego