Extra ID or extra fields in index
in [Help]
|
Prev: I was hypnotised!
Next: Teradata DBA , Richmond, VA
From: Lars Brownies on 16 May 2010 03:59 Thanks Bob, Salad, That helps. "Bob Quintal" <rquintal(a)sPAmpatico.ca> schreef in bericht news:Xns9D797C0E9E2BFBQuintal(a)69.16.185.250... > "Lars Brownies" <Lars(a)Browniew.com> wrote in > news:hsm6dj$peo$1(a)textnews.wanadoo.nl: > >> I have a person table and want to add an extra 1xM table: >> tblNotes. I'm doubting whether to add a specific primary key >> NotesID or to put a primary index over several fields: >> >> Solution 1: >> NotesID* >> PersonID >> DateTime_Entered >> UserID_Entered >> Note >> >> Solution 2: >> PersonID* >> DateTime_Entered* >> UserID_Entered* >> Note >> >> What would be best? >> >> Thanks, Lars >> > Normalization rules imply that you should not use a surrogate key > when a natural key is available, because it wastes storage space. > This means using solution 2 > > But rules were made to be broken. > > The surrogate rule is often broken to make database design simpler or > execution faster. This means using solution 1 > > In your case, flipping a coin to choose the solution will save hours > in deciding which structure to use. >
From: Access Developer on 18 May 2010 12:11
I remember that thread, think it was crossposted, and their were strong feelings in some "more theoretically-oriented" newsgroups, but believe your summary of the conclusion is accurate. I am in the "it's convenient to use a surrogate key for joins but you can always create a unique index on the multi-field natural key as the last-ditch defense to prevent duplicates" camp. So, as a matter of habit, almost all of my tables have an Autonumber key (but, like a lot of mere humans, that has to be quaified with "except when they don't" <SIGH>) -- Larry Linson, Microsoft Office Access MVP Co-author: "Microsoft Access Small Business Solutions", published by Wiley Access newsgroup support is alive and well in USENET comp.databases.ms-access "Salad" <salad(a)oilandvinegar.com> wrote in message news:5IedndPjH_WCR3PWnZ2dnUVZ_oSdnZ2d(a)earthlink.com... > Bob Quintal wrote: >> "Lars Brownies" <Lars(a)Browniew.com> wrote in >> news:hsm6dj$peo$1(a)textnews.wanadoo.nl: >>>I have a person table and want to add an extra 1xM table: >>>tblNotes. I'm doubting whether to add a specific primary key >>>NotesID or to put a primary index over several fields: >>> >>>Solution 1: >>>NotesID* >>>PersonID >>>DateTime_Entered >>>UserID_Entered >>>Note >>> >>>Solution 2: >>>PersonID* >>>DateTime_Entered* >>>UserID_Entered* >>>Note >>> >>>What would be best? >>> >>>Thanks, Lars >> >> Normalization rules imply that you should not use a surrogate key when a >> natural key is available, because it wastes storage space. >> This means using solution 2 >> >> But rules were made to be broken. >> >> The surrogate rule is often broken to make database design simpler or >> execution faster. This means using solution 1 >> In your case, flipping a coin to choose the solution will save hours in >> deciding which structure to use. > There was a thread, maybe 3 years ago, with a discussion on whether or not > to use an autonumber field as a primary key or leave it off. Me, I like a > primary key like an autonumber. It makes it easy to find records. Even > if I don't need to find a record, I prefer having a primary key. Just > habit. Some thought the use of an autonumber primary in some cases was > unneeded. The bottom line aka result of the thread seemed to be "I'll do > it the way I prefer". |