Question re composite key

[C Tate]

I have to design a database. It will contain details of planning
applications. I've been told there is not a single item which will identify a
site. There may be two reference numbers. I think this is a composite key.
Are there any important points I should know if I use a composite key? Will
it make my database more complicated to design/use?

 

[Evan Keel]

Composite keys are two or more columns that uniquely identify a row. They
can make your db harder to work with (joins for example) but add more
meaning than just a surrogate key. If you find that the number of columns
become unwieldy, then think surrogate key. For more info google "surrogate
key".

I'm not sure about two reference numbers used a composite key. Please post
the table structure you are considering..

Good luck!
Evan

 

[John W. Vinson]

I have to design a database. It will contain details of planning
applications. I've been told there is not a single item which will identify a
site. There may be two reference numbers. I think this is a composite key.
Are there any important points I should know if I use a composite key? Will
it make my database more complicated to design/use?

Access allows up to ten fields in a composite key, and you *can* use composite
primary keys linked to composite foreign keys. The queries can get pretty
snarky though! If you don't have any child tables related one to many to this
table, by all means use the composite key. If you do have child tables,
consider making a unique Index on the combination, and adding a "surrogate"
autonumber primary key, using it as the link to a Long Integer foreign key in
the child tables.

 

[Evi]

An Autonumber is a nice safe option and easy to use for lazy types like me.
You can still have your numbers but if someone decides that they don't want
to use one of them or they aren't as unique as you were led to believe, you
won't find yourself with an enormous problem. The two numbers can
concatenated in a query to make a unique number for combo boxes in forms
Evi

 

[binny]

If you substitute "name" for "number" we all have two reference codes
ie:first name. Last name.
Think of your 2 reference numbers like that, label them in your table,
first number,last number
But use an auto generated primary key to uniquely identify every record in
your table. In the same way you would in a contacts organiser.
The only purpose of the primary key is as a unique reference for the
database search engine. No one shows it on forms or in reports.
binny

 

[Guest]

You must have your key fields indexed in the same order in
both indexes, ie (ref_1, ref_2) = (ref_1, ref_2) not
(ref_2, ref_1), (ref_1,ref_2).

You were probably going to do that anyway ... but there is
no obvious indication if you get it wrong, it just doesn't work.

(david)