Unique Record Entry (1 Viewer)

[T-Pol]

I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table

When I do it, it comes back with an error: Index or primary key cannot contain a null value.

What am I doing wrong?

 

[jdraw]

You have misinterpreted something.
A table can have only 1 primary key.
And the PK can not be NULL.

 

[T-Pol]

I got the instruction from the Access Help site. I would send you a link, but as a newby, the system won't let me.

If this isn't the solution, can you recommend one?

 

[jdraw]

Here's a link to a lot of relational database design terms, examples etc.
http://www.tonymarston.co.uk/php-mysql/database-design.html#primary.keys

You should read this article for a great overview of relational data base.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

Good luck with your project.

 

[T-Pol]

You're right, I was using the wrong terminology. What I was looking for was help on setting up a 'Composite Primary Key'. I found it and it seems to be working.

What are the primary drawbacks of a Composite Primary Key?

 

[jdraw]

If you search a few forums you will see many debates of the pros and cons of natural vs surrogate keys; indexes vs keys, unique indexes; composite unique indexes; multiple keys vs multiple indexes,... it's interesting and a great technical debate, but there is no single rule that says you MUST do it this way or that in all situations.

The alternative to a composite primary key is a unique surrogate key (ie Access autonumber). But you will have to (usually) create a composite unique index on the fields you were going to use as a composite primary key to prevent the creation of duplicates.

Some people will say -- use a composite primary key on a junction table UNLESS there are children of that junction table.

Others will say -- always use a surrogate primary key (autonumber) and you will not get in trouble [ I would caution that unless you recognize situations where there is a need for unique composite indexes to prevent duplicates].

Drawback to composite primary key -- you must use a composite foreign key in related tables.

Good luck with your project.

 

[T-Pol]

Thank you very much for your help jdraw.