Help in selecting a primary key

[Jan H. DeGiorgio]

I have a simple database for tracking property sales which includes a
property information table and a sales information table.

I have been using a composite primary key which I now realize makes no sense
(i.e., composite of parcel number and the date I entered the transaction
into the database - why I thought the entry date was significant I have no
idea as I could enter the same property the next day and wouldn't violate
the unique rule).

I'd like to change the primary key to something that makes sense but can't
think of one that wouldn't violate some rule or another.

1. A property will sell many times over the years so just using the parcel
number won't work.

2. Autonumber makes no sense as I could enter the same property repeatedly
with different auto numbers.

3. A combination of parcel number and date of sale would work but would be
burdensome to enter (about 22 characters) and would require me to have the
sales date field in the property table instead of the sales data table where
it belongs (violating a normalization rule).

I'm probably missing the obvious and would appreciate any suggestions as to
a reasonable PK (bearing in mind I'm going to have to backfill nearly 3000
records).

Thanks to all.

JanDG

 

[Allen Browne]

Jan, to trace sales of a parcel over time, you would need two tables:
1. A table of parcels (on record for each parcel of land);
2. A table of sales.

The Parcel table would have a primary key of some kind: preferably an
AutoNumber so that your database is not stuck when the governmental
authorities mess up their parcel numbers.

The Sale table would have its own primary key (another autonumber), as well
as a foreign key to the Parcel table.