Other Software > Developer's Corner

Programming 101 Lesson: Don't Purge User Data

<< < (4/5) > >>

Lashiec:
A simple but very clever idea. I like those ones :D

OGroeger:
How do you face the situation when a record has been moved to the archive and afterwards a doublette has been inserted in the live db? I mean

* a record has been moved to the archive
* Someone inserts a new record with the identical business key as the archived record
* The new record dates out and will be moved to the archive
I'm sorry to pester you with questions.

 

tinjaw:

* Someone inserts a new record with the identical business key as the archived record-OGroeger (October 04, 2007, 04:48 AM)
--- End quote ---

There is your problem. You should never reuse such a key. Unique Primary Keys are the heart and soul of any database.

Ralf Maximus:
No problem!  Glad to help.

The system design prevents duplicating an archived key in most cases, and in other cases we kind of shrug in a distracted manner and mutter "don't care".  Multiple duplicate keys aren't a problem, since all the ACTUAL key fields used to relate tables together are hidden from the user and remain unchangable.  We NEVER allow user-manipulated data to link records together, so if somebody deletes the Master Record ID for a chunk of data, all they're doing is erasing a cosmetic item.  The system chugs on.  Likewise of they change a Customer ID to match another Customer ID the REAL linkages behind the scene don't change, but the next time they query for that ID they'll see two items listed in the results.  And BTW if users are doing this kind of thing, they sort of get what they deserve.

The system was designed with the basic understanding that humans are very clever at entering duplicate data no matter how hard you try to stop them.  We accept that and assume (some) duplication exists, but allow users to clean it up when they want.

Part of it is training, also.  Before creating (say) a new Customer record, it is strongly urged that the user perform a search first.  When the search fails (both live and archived data) only then is it a good idea to create a new Customer record with unique Customer ID.  And the machine generates all the unique IDs, not the user.

Ah, but what about those diabolical customers who change their names everytime they call?  In 2005 when they signed up they used "BRIAN T. DAMAGED" but now when they call back they're "BRIAN DAMAGED".  Next time it might be "B.T. DAMAGED".... and if a user's not careful they might end up with three separate records for the same guy.

Well fortunately, the system does some pretty comprehensive searches.  Querying for "BRAIN DAMAGER" would still match the original 2005 record.  And all that's assuming the user doesn't know their unique Customer ID number, which oddly enough most of them do. 

But I digress.

So what would happen in your suggested scenario, where the 2005 "BRIAN T. DAMAGED" has been archived, and some n00b creates a new 2007 "BRIAN T. DAMAGED" despite there already appearing to be a record for the guy?  Despite the thunderous warnings from the system, the hooting alarms, the electrical shocks?

The system happily accepts the duplicate record, and will eventually archive it alongside the 2005 version when it grows old enough.  If a smarter user stumbles across the two records and decides they should become one, it's a menu option to merge them.

Seems to work well, so far.  And most of the above still applied before we implemented the archiving feature.

Does that make sense?

OGroeger:
@tinjaw:

There is your problem. You should never reuse such a key. Unique Primary Keys are the heart and soul of any database.
-tinjaw (October 04, 2007, 08:29 AM)
--- End quote ---

The emphasis is "business". A business key is something that you can use to distinguish between records and to find and identify records, e.g. a user name when the your entity is user, or an isbn nummer when you manage books, or a social card number. It is something that you would use in the "from" part of a select statement. You should not use this as primary key. The primary key should not have a business meaning, only a technical. Best is you let the database manage the primary key, for instance in ms sql use an identity column. The background of my question to Ralf Maximus was that i was interested whether his business key is unique or not.

@Ralf Maximus:
Absolutely yes, this sounds mature and well designed. Thank you for the inside view.

Navigation

[0] Message Index

[#] Next page

[*] Previous page