Primary Key - Unique ID AND Unique SN ??

[LaRetta]

Okay ya'all ... I'm redefining some major structures at present. I want to resolve for myself, once and for all, the issue of unique primary keys and ID's. WHEN should a 'random' Unique Primary Key (such as CaptKurt's formula) be used and when is okay to use ID numbers, such as Client ID 1804, 1805; or Invoice # 28101, 28102, etc. as Primary Keys? Or, should I have both ALWAYS -- an ID for the Key AND a 'serial number?'

I would assume that CaptKurt's Unique Primary Key is VITAL for large-volume unique records, particularly if import/exporting, sychronizing, etc. -- but having BOTH a Unique ID Key AND a Unique 'serial number' appears to be overkill. Any final input before I restructure this thing?

[LaRetta]

Sorry ... so, in other words, should EVERY record in EVERY db ALWAYS have a 'unique through the history of the world' serial number AND a Unique join key, so the SN can be used for merging, etc. I sure hope my question is clear!

[Ocean West]

One thing to consider about using IDs like Client ID etc is make absolutely sure you have complete control over it and never allow user entry on that field. Once a record is created via a script attached to a button, that ID should never change.

This is extremely vital when you have children records associated with them. Example Invoice and Line items. Each line item is assoicated with the invoice. If ever that invoice ID changes it will orphan every line item on that invoice and you'd spend considerable time trying to find the orphans and re linking the data.

I use to be of the same opinion that it was an over kill. But rember in many developments the design perameters can and will change. For example I had a solution where I had to create a unique alias for invoices because half way through the project they decided to Number their invoices instead of having the computer generate the number. Which meant I had to allow for manual entry of the invoice number.

If you already have this unique id there it could make design changes easier. Just something to think about.

[LaRetta]

That was some of my thinking ... an ID may be unique now and suit my purpose, but what about the future? Then I thought, I don't want to bloat my dbs -- but I think that, if I'm going to bloat them, this is the time, place and most certainly the motivation. Thank you for responding

[Kurt Knippel]

Here is my basic philosophy in this situation. Always use both, give one to the control of the customer or user of the solution (i.e. CustomerNumber) and keep you CustomerID_pkey for the actual internal relationship use.

This way the customer/user has a number that they can treat in whatever way they wish and it does not disrupt the integrety of the solution.

Every customer that I have worked with who already had a "unique indentifier" had so many duplicates as to make it almost a joke.

To follow through with OceanWest's example of the Invoice Number change, this could be done as many times and with as many different schemes as desired, but it never once touches on the InvoiceID, which is what is actually used to related the records.

Maintaining this duality of IDs also makes it easy to do cascading updates, as you have a method of linking the records together that will never change and can be used to update all the information that does change.

[LaRetta]

Thank you Kurt. This is what I NEEDED to hear, although not what I wanted to hear. With my 22-db many-to-many structure currently disconnected, it sounds like now is the time to re-establish the correct joins, i.e. NOT on Invoice# but it's internal (never-to-be-duplicated) unique InvoiceID and, although the Invoice# is set to auto-enter s/n, do not allow modification -- I will never be faced with orphans, unidentified data, duplicates or major data-mappings again. It's worth the effort to put into effect. And, SETting through a series of records with your formula is, after all, very easy.

Well, wait ... If I use InvoiceID (unique to Invoice db) to join to LineItems:InvoiceID, won't I still want a field LineItemID for it's particular unique needs? Why do I make things so complex? I would greatly appreciate your input on this final dichotomy

[LaRetta]

One more question, I promise the last (at least on this subject I am still in the middle of data migration. If I establish the UniqueID (text, auto-enter formula), SET the existing fields, and THEN import new data, will the auto-enter treat the newly imported records as 'new records' and continue assigning the ID or will I need to SET each imported batch?' I'll be glad when this is finished ... I dislike straddling a creek

[Kurt Knippel]

I generally will put a unique ID into every file, even if I do not think that I will ever use it. The overhead caused by this is miniscule and invariably there comes a time when you need it.

If you check the option on the import to set the auto-entered data, then yes. However this changes creation and modification dates and such, so I generally just do a replace once the import is complete.

[LaRetta]

PERFECT! Implementation now underway! Wish me luck and ... yes, I'll backup between EACH step!