serial uniqueness - again

[stefangs]

i'm back with serial keys, my fav confusion topic. for the longest time, i've used timestamps as serials, but now i've heard that normal (hidden) auto-entered serial numbers are best to use as keys. presumably, the child end of these keys use numbers, which are not auto-entered, correct? now imagine this:

User1 has a copy and creates some records.

User2 living somewhere else also has a copy and creates some records.

User3 wants to get the combined knowledge and asks User1 and User2 for their copy and imports their data.

User3 now ends up with lots of duplicate serial numbers and non-related stuff showing in portals.

in this scenario, it seems that auto-entered serials do not work. what would you suggest?

[Simon K]

In this scenario (remote databases that need to be synchronised back together) neither timestamps nor auto entered serials will "work" - that is to say that neither will guarantee unique primary keys.

One method would be to create a table which contains a master set/pool of ID's (with or without prefixes for each table) that is allocated to each site database and "lookup" from that pool the next unused ID record - either the prefixes and/or the number sequences allocated can then be used to give a unique primary.

HTH

Simon

Edited September 14, 2008 by Guest

[stefangs]

thanks for your reply. that would work well if the number of sites is limited or known.

what do you think of a concatenation of

Get (SystemIDAdress) & TimeStamp.

should be pretty unique, yes? except i never even tried the ipadress field for anything.

[stefangs]

i meant SystemIPAdress, of course, not SystemIDAdress.

[comment]

See:

http://www.fmforums.com/forum/showpost.php?post/286535/

http://fmforums.com/forum/showtopic.php?tid/194785/

[stefangs]

oh yes, here's something to learn! thanks!!

[Simon K]

Three things I don't like about this (you may have other views)

1) IP addresses do change and if your sites are using a DHCP server then maybe some room for error - if your client is maintaining the system then loads of room for problems

2) I feel that you need "sequentiality" too and given the problem of (1) I think fixing/auditing data may be very difficult with a timestamp element to the record

3) The length/complexity of the ID will be very human unfriendly

Simon

[Simon K]

Hadn't seen Michaels response - apologies

[stefangs]

good point with that DHCP router. in general, i think the approach is quite good, although i don't see what advantage the timestamp brings. seems like the ip address combined with serial would be enough.

[El_Pablo]

Use the system MAC address.

I had the same problem. I've done a workaround using the system MAC address combined with a serial number.

Since the MAC address should be unique, each record should have it's unique key.

pkRecordID = serialNumber & Substitute(Get (HostNICAddress); ":"; "")

Where serialNumber is a ... serial number field.

Check the last message

[stefangs]

did you mean hostIPAddress? i can't find hostNICAdress, but perhaps it was added in FM9 which i don't have.

either way, this sounds more solid since in either case only one mac is used to supply the serial and presumably it does not matter if it's the remote or the local machine.

thanks, i'll go with that.

[El_Pablo]

My mistake the function is Get (SystemNICAddress).

Actually the serial number can be duplicate on any machine, but the MAC address should be unique for each client.

Edited September 16, 2008 by Guest

[stefangs]

ok - one last queston though (i hope):

Get (SystemNICAddress) returns two return delimited adresses on my mac. i assume that's because it has two ethernet ports. they differ only in the last digit. seems like it would be ok to just trash one of them as part of the substitution calculation you suggest to replace the colons. that would make the key field a bit smaller. or would you suggest to keep both of them?

[El_Pablo]

It would be simplier to keep only the first one since not all machine have two MAC address and yes it will be shorter.

Edited September 16, 2008 by Guest