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

On 5/15/10 6:09 AM, Lars Brownies wrote:
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.

Thanks for the outline. That will get me there. Just to make sure I
understand:

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?

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 (AT) Republic (DOT) com> schreef in bericht
news:4BEEA592.4080404 (AT) Republic (DOT) com...

"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote in
news:hsm6dj$peo$1 (AT) textnews (DOT) wanadoo.nl:

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.

Bob Quintal wrote:
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".

Thanks Bob, Salad,
That helps.

"Bob Quintal" <rquintal (AT) sPAmpatico (DOT) ca> schreef in bericht
news:Xns9D797C0E9E2BFBQuintal (AT) 69 (DOT) 16.185.250...

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 (AT) oilandvinegar (DOT) com> wrote