Primary key on one or two fields
Date: 6 Aug 2003 03:21:01 -0700
Message-ID: <213b8522.0308060221.12f06132_at_posting.google.com>
I am currently trying to design a database structure for some data. I currently have the following information: Rates: These have a name and a type.
Rate revisions: Each rate has at least one revision and date for each revision
Rate revision employees: Each revision has a rate (float) for each employee.
The Rates table will just have RateId, RateName and RateType.
My question concerns the RateRevisions table. There are two ways of doing it.
First:
Each rate has a unique RevisionId (I can get the database to generate
this using autonumber/identity). The other fields are the RateId and
RevisionDate. The primary key (used to reference from the
RateRevisionEmployees table) is the RevisionId.
Second:
The records are uniquely defined by a combination of the RateId and
RevisionId. When I add a new revision, I find a RevisionId that is
unique only for that RateId (e.g. select max(RevisionId)+1 From
RateRevisions where RateId=?). The primary key becomes the RateId with
the RevisionId and these two fields are used in RateRevisionEmployees.
The first way makes things easier because I can get the database to generate the RevisionId. Also, if I want to join the Revisions table to the RateRevisionEmployees table (where the actual rates are stored), I can do it on just one field rather than two.
However the second way feels neater. This is because I am using the minimum number of bits of information to define the record (the RevisionIds will be smaller). It would also allow me to join the RateRevisionEmployees table directly to the Rates table.
Do any of you theorests have an opinion on the correct way to deal with this situation?
Regards,
Jonathan Received on Wed Aug 06 2003 - 12:21:01 CEST