Extra ID or extra fields in index
in [Help]
|
Prev: I was hypnotised!
Next: Teradata DBA , Richmond, VA
From: Lars Brownies on 15 May 2010 09:09 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
From: Banana on 15 May 2010 09:45 On 5/15/10 6:09 AM, Lars Brownies wrote: > 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 You say 1xM. Therefore, it has to be solution 1. Solution 2 is more expensive for no good reason. Besides, if you really wanted to prevent duplicate of #2: CREATE TABLE Notes ( NotesID AUTOINCREMENT PRIMARY KEY, PersonID INT NOT NULL, DateTime_Entered DATETIME NOT NULL, UserID_Entered INT NOT NULL, CONSTRAINT fkPeopleNotes FOREIGN KEY(PersonID) REFERENCES People(PersonID), CONSTRAINT uniqueNote UNIQUE (PersonID, DateTime_Entered, UserID) ); This will give you the simplicity of #1 and prevention duplication equally well as #2 at expense of maintaining three indexes instead of two for either solution. Whether the expense is worth it, is a decision you need to consider. Personally I'm inclined to say #1 is good enough - it'd be exceedingly unlikely that a duplicate of { PersonID, DateTime_Entered, UserID } would actually occur.
From: Lars Brownies on 15 May 2010 11:24 Thanks for the outline. That will get me there. Just to make sure I understand: > You say 1xM. Therefore, it has to be solution 1. I'm not sure I understand. With solution 2 you can have 1 person with 1 or more notes. Why isn't that a 1xM relationship? > Besides, if you really wanted to prevent duplicate of #2: In fact that's not my goal. The only reason for putting an index on three fields is to prevent that a user is blocked from adding more than 1 record. If PersonID would be the only field in the primary index the user could only add 1 note to one person. Lars "Banana" <Banana(a)Republic.com> schreef in bericht news:4BEEA592.4080404(a)Republic.com... > On 5/15/10 6:09 AM, Lars Brownies wrote: >> 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 > > You say 1xM. Therefore, it has to be solution 1. Solution 2 is more > expensive for no good reason. Besides, if you really wanted to prevent > duplicate of #2: > > CREATE TABLE Notes ( > NotesID AUTOINCREMENT PRIMARY KEY, > PersonID INT NOT NULL, > DateTime_Entered DATETIME NOT NULL, > UserID_Entered INT NOT NULL, > CONSTRAINT fkPeopleNotes FOREIGN KEY(PersonID) > REFERENCES People(PersonID), > CONSTRAINT uniqueNote UNIQUE (PersonID, DateTime_Entered, UserID) > ); > > This will give you the simplicity of #1 and prevention duplication equally > well as #2 at expense of maintaining three indexes instead of two for > either solution. Whether the expense is worth it, is a decision you need > to consider. Personally I'm inclined to say #1 is good enough - it'd be > exceedingly unlikely that a duplicate of { PersonID, DateTime_Entered, > UserID } would actually occur.
From: Bob Quintal on 15 May 2010 12:11 "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: Salad on 15 May 2010 13:30
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". |