Porter, Mark
MPorter at acsalaska.com
Fri Jun 4 03:24:09 CDT 2004
I'm working with an enterprise application where the data model is 99.99% pre-made. However, how we use this data model is flexible to our business needs. In this case, the actual data model only describes the data held within, it is not an actual representation of it. Even the table names and fields are sometimes completely different from the data that we put into it (the GUI labels are changed, of course). It is even recommended that, should we require a new column, we use one of the many unused colums in the overbuilt data model rather than create another one in the data schema. The only hard database object which reflects the actual data in the model is the 'user key', a unique index on the table which describes what our businesses uses as a natural key for the data which we put into the table. The tables all have a single unique Identifier column (effectively an autonumber for the sake of our discussion), and audit fields such as creator, last changed by, create date, change date, etc. that describe attributes of the record in the table (as opposed to the data in the table). The columns above have nothing to do with the data held within the data model, yet they are integral to the working of the system itself. In this case you have two keys for two purposes; A natural key for the DATA, and a unique identifier for the RECORD. Where do you draw the line between what a pure data model should be (i.e. - nothing in the data model that is not a part of the data itself) and enhancing the system with information that describes the data? Mark > -----Original Message----- > From: Arthur Fuller [mailto:artful at rogers.com] > Sent: Thursday, June 03, 2004 5:50 PM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] OT: The Great Primary Debate > > > It's actually more complicated than that, and while I fall on the side > of ANPKs, I do respect the opinions of such luminaries as Joe > Celko, who > despises ANPKs. His reason? They model nothing in the real world; his > premise -- any data model that artificially maps real-world entitities > is by defnition flawed. > > He works in different environments than I. Typically I think of the > egg-carton, and question the significance of which two eggs I > choose to > make my morning omelet. He thinks in terms of "this alternator was > manufactured by XYZ for use in Ford models T, U and V. It was > manufactured on a day in a factory and signed off by employee ABC as > valid and working. Or to put his argument another way, I am a > vendor of > antiques and I have precisely one Queen Anne male chair (the > difference > between male and female being the armrests or lack thereof), which was > hand-crafted by Hortense Witherspoon circa 18whatever. > > If I read him correctly (which, given my rapidly increasing senior > moments, may be a faulty assumption), these cases (as opposed to the > eggs in a carton) can all be given primary keys which derive from the > data themselves, and do not falsify the picture by > introducing an ANPK. > > I don't mean to resurrect the PK debate, nor to say that I agree with > Joe. Concerning the latter, I emphatically do not agree. But he is a > luminary that has written some brilliant books and probably > makes twice > the money that any three of us on this list do. Oracle and > DB/2 seem to > be his favorite turf, but I could be wrong about that part. > > Anyway, I stand clearly on the side of ANPKs and have found none of > Joe's arguments on this subject persuasive. But as he wrote > to me in an > email a while back, "Go take a data-modeling course." Well, I took his > advice and took a data-modeling course, and I remain unpersuaded. > > Arthur > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules, > Mark S. (Newport News) > Sent: Wednesday, June 02, 2004 9:07 AM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] OT: The Great Primary Debate > > > As a potential hypothesis, I would have to agree. But in > order to prove > your hypothesis you should have presented the opposite "Fear > Factor" as > well:) That those in favor of AutoNumbers fear the user's ability to > screw up even a five-field compound key by unwittingly uncovering the > one situation in which it would fail;) And, that those in favor of > AutoNumbers fear the complexity and never-ending maintenance that will > "inevitably" result from such a decision;) > > > Mark > > > > -----Original Message----- > From: Lawhon, Alan C Contractor/Morgan Research > [mailto:alan.lawhon at us.army.mil] > Sent: Wednesday, June 02, 2004 8:51 AM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] OT: The Great Primary Debate > > > Martin, Susan, John, Jim, Charlotte, Drew, Gustav, et al: > > I think there is another factor involved in this "AutoNumber versus > Natural Key" PK debate. For lack of a better word or terminology, I'm > going to refer to it as the "Fear Factor" or a fuzzy type of > generalized > apprehension. This "apprehension" boils down to something along the > lines of, "Well, what happens if the AutoNumber field gets > corrupted or > somehow those autonumbers get jumbled or out-of-sequence? If that > happens, then how do we re-establish the primary keys and make sure > they're associated with the correct records?" (The more records there > are in a table, the more heightened this fear or apprehension tends to > be.) > > The answer to this [unspoken] question is that they would rather avoid > the possibility altogether by using non-Autonumber composite (i.e. > "Natural") primary keys. It appears, from my experience, > that folks who > have not been trained in database theory seem to have an intuitive > preference for natural keys - even when such "natural" keys > involve the > concatenation of two (or > more!) fields - with all the headaches that come from trying to manage > such an unweildy arrangement. > > I have experienced this issue firsthand here at work. We are > managing a > substantial (several million record) environmental database with > multiple linked tables, numerous views, action queries, macros, et > cetera ... This application requires primary and foreign > keys in nearly > all of the base tables. Early on we tried to persuade the senior > project engineer, (a chemical engineer by profession), of the > wisdom of > using single-field AutoNumbers for the PK in the most > important table of > the application. He adamantly refused, insisting on a two-field > composite primary key. We did our best to try and persuade him that an > autonumber PK was best, but he wouldn't hear it. Since this engineer > has major input into our performance appraisals, the programming staff > acquiesced. We decided to live with a "composite" PK - even if we > didn't like it or agree with it. > > After giving this a great deal of thought, I have come to the > conclusion > that folks who prefer "natural" (composite) PKs do so due to a general > apprehension or mistrust of AutoNumbers. I don't think it is a > "technical" issue, but rather a "people issue" centering > around fear and > apprehension. Whenever one sees a strong visceral reaction, (such as > what Martin saw yesterday), this tends to reinforce my perception that > this is more of a "people problem" than a purely "technical" problem. > > Does this make any sense to the rest of you? > > Alan C. Lawhon > > > -----Original Message----- > From: Martin Reid [mailto:mwp.reid at qub.ac.uk] > Sent: Tuesday, June 01, 2004 1:17 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] OT: The Great Primary Debate > > > I was taking day one of a 4 day Programming SQL Server 2000 course > today. 8 Oracle programmers moving to SQL Server, 6 of our Ingres > programmers moving to SQL Server. > > Came to the section on Table Design. I said use an Identity value for > the PK on the table - all h%ll brooke loose for the next hour as the > great debate happened live in person. Pity JC wasnt there to > back me up > (<: Was split between the younger developers who supported the use of > the ID column and the older developers and DBAs who use natural keys. > Almost a 50//50 split on age lines maybe reflecting different > attitutes > to design. Took me about 20mins with one of the older guys to explain > how the relationship was maintained using Idt IDs as opposed to his > staff number. He seemed to have real problems getting the concept. > > Martin > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > *********************************************************************************** 4/6/2004 This transmittal may contain confidential information intended solely for the addressee. If you are not the intended recipient, you are hereby notified that you have received this transmittal in error; any review, dissemination, distribution or copying of this transmittal is strictly prohibited. If you have received this communication in error, please notify us immediately by reply or by telephone (collect at 907-564-1000) and ask to speak with the message sender. In addition, please immediately delete this message and all attachments. Thank you. ACS