Linking tables when there is a multiple-field primary key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was trying to figure out how to concatenate several different fields
automatically into a separate field to make a unique key for the table. It
was suggested that instead, I use the several fields together to make the
key, rather than duplicating the data just to make a key field. Now I am
trying to link to this field, and I don't know how to link to all 4 fields
that are used for the key, instead of linking to a specific field. Is this
possible? Should I go back to making a single field that I would then be
able to link to? Is there a better way? Thanks for your help.
rg
 
Should I go back to making a single field that I would then be
able to link to?
Click to expand...

That is my recommendation. Use a surrogate autonumber primary key, which you
can join to a number (long integer) data type foreign key. If you wish, you
can create a multi-field index on your four fields, with Unique set to Yes,
to eliminate the possibility of any duplicates for the four fields taken
together.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Should I go back to making a single field that I would then be
able to link to?
Click to expand...

That is my recommendation. Use a surrogate autonumber primary key, which you
can join to a number (long integer) data type foreign key. If you wish, you
can create a multi-field index on your four fields, with Unique set to Yes,
to eliminate the possibility of any duplicates for the four fields taken
together.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Do you mean that you are trying to create a query? You need to
join each of the fields. Also, to get an updatable query, there
needs to be a matching index on each side.
On one side, the index will be the primary key index. On the
other side, you need to create a multi-field index with the fields
in the same order as in the primary key index.

I normally use a number as a surrogate primary key. This is partly
because I was a programmer before I was a DBA. Code design theory
always uses surrogates: it's called data hiding. DB theory tends to
favour natural keys: the whole idea of a database is to expose data
in an efficient and natural way.

There is no single best answer to that question. The reason people
disagree is because there are arguments for both sides. However,
if your problems are more on handling the data (if you have a trivial
application or a massive data store) you would lean to the DB theory.
If you need to do a lot of coding and re-design, you would lean to
the coding ideas.

Since people use Access/Jet for agile development, not massive
transaction farms, as a group we tend more towards surrogate keys.

Creating a surrogate key by concatenating strings is not a good
idea. If you choose to use a surrogate key, just use numbers.

(david)
 
Do you mean that you are trying to create a query? You need to
join each of the fields. Also, to get an updatable query, there
needs to be a matching index on each side.
On one side, the index will be the primary key index. On the
other side, you need to create a multi-field index with the fields
in the same order as in the primary key index.

I normally use a number as a surrogate primary key. This is partly
because I was a programmer before I was a DBA. Code design theory
always uses surrogates: it's called data hiding. DB theory tends to
favour natural keys: the whole idea of a database is to expose data
in an efficient and natural way.

There is no single best answer to that question. The reason people
disagree is because there are arguments for both sides. However,
if your problems are more on handling the data (if you have a trivial
application or a massive data store) you would lean to the DB theory.
If you need to do a lot of coding and re-design, you would lean to
the coding ideas.

Since people use Access/Jet for agile development, not massive
transaction farms, as a group we tend more towards surrogate keys.

Creating a surrogate key by concatenating strings is not a good
idea. If you choose to use a surrogate key, just use numbers.

(david)
 
I was trying to figure out how to concatenate several different fields
automatically into a separate field to make a unique key for the table. It
was suggested that instead, I use the several fields together to make the
key, rather than duplicating the data just to make a key field. Now I am
trying to link to this field, and I don't know how to link to all 4 fields
that are used for the key, instead of linking to a specific field. Is this
possible? Should I go back to making a single field that I would then be
able to link to? Is there a better way? Thanks for your help.
rg
Click to expand...

As Tom and David suggest, there are better ways to do this. Building
an "intelligent" key by jamming together several disparate fields into
one new redundant field is probably your *worst* option.

To use the natural key, you need all four fields in each table. In a
QUery, you would join the first field to the first, the second to the
second, and so on - four lines in the design window. If you want to
change the join properties (say to an Outer Join) you need to do so
four times, one on each join.

Note that if you do take this option, if you haven't done so already,
you should open the "one" side table in design view; ctrl-click the
four fields which jointly constitute the key; and click the Key icon
to define them as the joint four-field Primary Key.

John W. Vinson[MVP]
 
I was trying to figure out how to concatenate several different fields
automatically into a separate field to make a unique key for the table. It
was suggested that instead, I use the several fields together to make the
key, rather than duplicating the data just to make a key field. Now I am
trying to link to this field, and I don't know how to link to all 4 fields
that are used for the key, instead of linking to a specific field. Is this
possible? Should I go back to making a single field that I would then be
able to link to? Is there a better way? Thanks for your help.
rg
Click to expand...

As Tom and David suggest, there are better ways to do this. Building
an "intelligent" key by jamming together several disparate fields into
one new redundant field is probably your *worst* option.

To use the natural key, you need all four fields in each table. In a
QUery, you would join the first field to the first, the second to the
second, and so on - four lines in the design window. If you want to
change the join properties (say to an Outer Join) you need to do so
four times, one on each join.

Note that if you do take this option, if you haven't done so already,
you should open the "one" side table in design view; ctrl-click the
four fields which jointly constitute the key; and click the Key icon
to define them as the joint four-field Primary Key.

John W. Vinson[MVP]
 
The natural "key" for this data would be the combination of the four fields.
Ex.: OV.1.7.5
This is the code used for each piece of information that will be stored in
the table. Each of the four parts represent something specific about the
information and I have separate tables for each part. All the options for the
"OV" part in in one table (Strands), the "1" part is in another table
(Standard), the "7" is the grade level, and the "5" is the specific record in
the child table. The entire thing (OV.1.7.5) is how the information is
identified by those who are working with my database, that's why I would like
to make it the primary key. However, to keep from having problems when they
enter the key wrong, I want to have the key built as they enter the separate
pieces of info. such as Strand, Standard, Grade...
To keep from repeating the Strand and Standard information for each record,
I've created separate tables for each, with thier own info. which is
referenced through the link from the child table. Maybe I'm going about this
all wrong, it's been over ten years since I was a database designer and I'm
finding myself quite rusty.
-rg
 
John,
Please see my reply to Tom, where I give more info on what I'm trying to
accomplish and why. I would appreciate any suggestions you have.
-rg
 
Dear RG:

There are large advantages to not creating a surrogate key (autonumber).
There is no need to change how you are doing this.

It is perfectly possible to build your database using multiple column
natural keys.

When you write a query, you can JOIN on multiple columns. This is done with
the same drag and drop interface, or with a compound ON statement. Is this
where you're having difficulty?

If you create the relationships between these tables, the JOINs will be
constructed for you automatically in the Query Design View.

In the relationship window, you can also drag and drop to create
relationships on multiple columns.

None of this is at all difficult. Done well, it will create a smaller,
better performing database.

You should have this disclaimer. This is probably the number 1 debate among
database designers, year in and year out. Mainly, do what you're
comfortable doing. If you're already comfortable making natural key joins,
then by all means, keep doing it.

In a couple of months I propose to publish what will hopefully be a
definitive work on this subject.

Tom Ellison
 
Back
Top