"Dan" <megaflex (AT) bellsouth (DOT) net> wrote

Hi Dan,

You are correct if you are relying upon the value of the primary key for
uniqueness across databases. This is nothing new and is one of the first
problems you come across when syncing databases for example. The Solution is
that you need to have an Database ID that when combined with any Primary Key
Value generates a totally unique ID for that particular table/database so
that if you export and then import into a foreign database there will not be
any unexpected collisions. FYI: I also happen to store my Database ID in the
"next number" table.

Leslie.

Leslie,

So, you're using a compound Primary key?

The reason I'm asking is this... If you have an inventory system (as
example) that keeps the same items at different locations, I would want the
primary key on the unique item identifier value... (that may be a SKU
number)... But that same SKU may also exist at another location (another
database)... To combine all location databases would then create a
problem...

So, I identify each location with a unique 4 character value... I then
combine this 4 character value with the SKU value to create my primary
key... it is not a "compound key".... but the value in my primary key is
actually the combined value of Location and SKU. This way, I avoid
compound primary Key fields

Now, when seperate databases are compined, there will never be a primary key
conflict.

Do you do it this way, or actually use a compound primary key?

--
....
`·.¸¸.·´¯`·.¸¸.·´¯`·-> rodney

"Rodney Wise" <NSpamPlease_rodney1 (AT) bellsouth (DOT) net> wrote

Hi Rodney,

You are correct, a compound key is not required (nor desirable).

In my case, the Primary key is (currently) A14. It is populated by getting
the DatabaseID and then concatentating the Next Number.

The DatabaseID is the Date and time (to the millisecond) in numeric format.
This numeric format has been converted to octal and then into ascii (a total
of 7 bytes) - The value is almost unique. My Next Number is the number in
ascii format.

As you have said, a compound key is really too troublesome in that it
restricts you to having to write your own export facility (which I have got
in the form of a sync engine). By using a single primary key you can use any
application to extract the records and still have uniqueness.

Leslie.