Does each related table have to have their own primary key assigne

[new2datamgmnt]

I have the following tables set up (I intentionally didn't show all the
fields of all tabls) & need to make sure I have the relationships set up
correctly before I enter large volumes of data:

General Policy Info:
Policy Number:Text=primary key

Policy Payment Schedule: no primary key set up on this table
Policy Number: text
Payment Date
Payment Amount

Endorsement Detail: No primary key set for this table
Policy Number: Text
Endorsement Date

when I view the "relationships" it shows 1-infinity lines extending from the
main table to the each of the other tables to the other 2 tables (joined at
the policy number field for each of the other 2 tables).

Do I have this set up correctly? I am not sure if I should have a primary
key set for each of the other 2 tables and if so, why would I need it?

 

[Vincent Johns]

I have the following tables set up (I intentionally didn't show all the
fields of all tabls) & need to make sure I have the relationships set up
correctly before I enter large volumes of data:

General Policy Info:
Policy Number:Text=primary key

Policy Payment Schedule: no primary key set up on this table
Policy Number: text
Payment Date
Payment Amount

Endorsement Detail: No primary key set for this table
Policy Number: Text
Endorsement Date

when I view the "relationships" it shows 1-infinity lines extending from the
main table to the each of the other tables to the other 2 tables (joined at
the policy number field for each of the other 2 tables).

Do I have this set up correctly? I am not sure if I should have a primary
key set for each of the other 2 tables and if so, why would I need it?

It looks OK to me, assuming (as I expect is true) that one policy may
have several associated payments and several associated endorsements.

You might (later) need a primary key in the other Tables for some types
of calculations, but it's not difficult to add one. Just open the Table
in Table Design View and add an Autonumber type field, and make that
field the primary key. You can do that at any time, when you find you
need it.

-- Vincent Johns <[email protected]>
Please feel free to quote whatever I say here.

 

[new2datamgmnt]

Thank you for your prompt response! It is greatly appreciated, and I am sure
that over time as I continue to function in the database I will need you
again, so once again thanks a lot! I also appreciate the forward thinking in
your reply with regards to "how to set a primary key later".

 

[Rick Brandt]

I have the following tables set up (I intentionally didn't show all
the fields of all tabls) & need to make sure I have the relationships
set up correctly before I enter large volumes of data:

General Policy Info:
Policy Number:Text=primary key

Policy Payment Schedule: no primary key set up on this table
Policy Number: text
Payment Date
Payment Amount

Endorsement Detail: No primary key set for this table
Policy Number: Text
Endorsement Date

when I view the "relationships" it shows 1-infinity lines extending
from the main table to the each of the other tables to the other 2
tables (joined at the policy number field for each of the other 2
tables).

Do I have this set up correctly? I am not sure if I should have a
primary key set for each of the other 2 tables and if so, why would I
need it?

In a properly designed database EVERY table has a primary key. In your case I
would either add an AutoNumber to the related tables to use as the PK or I would
use a composite key consisting of the Policy Number and the Date field.

The latter would be preferable to a db "purist", but I am not comfortable using
date fields in my PKs because this can be problematic when working with ODBC
tables instead of Jet (mdb) tables. If I thought that the tables would ever be
ported to SQL Server or similar I would likely use an AutoNumber instead of the
composite key.

 

[new2datamgmnt]

If my relationships are showing 1-infiinity, what would the purpose be for
having each table assigned a primary key (I would use the autonumber option
if it comes to that)?

The reason I ask is that when I was 1st setting up my database I had the 1st
table set with a primary key I assigned (policy #) and I set the next table
with an autonumber, but I was getting error messages when I was trying to do
reports and forms extrapulating data from both tables. So if you could just
explain the significance of having a primary key set on each table (keep in
mind that the subsequent tables, the ones with no primary keys currently set,
will never be primary tables for other tables. All my tables will always
come back to the the one I currently have the policy # as the PK.

I appreciate your input.

 

[Rick Brandt]

If my relationships are showing 1-infiinity, what would the purpose
be for having each table assigned a primary key (I would use the
autonumber option if it comes to that)?

The purpose is to have a properly designed database. A table without a PK is
just a file filled with data.

The purpose of a table is to store instances of an "entity". There should be
some way to distingush all of these entities from each other. That is what the
PK does.

 

[new2datamgmnt]

I will go into each of them and set an autonumber for the primary key.

However, by adding the primary keys does this by its very nature mandate I
perform other functions in the forms, queries and reports I have already set
up so as not to compromise the integrity of the newly updated database? If
the answer is yes, if you could kindly walk me thru the steps of the update
required for the forms, queries and reports.

 

[Rick Brandt]

I will go into each of them and set an autonumber for the primary key.

However, by adding the primary keys does this by its very nature
mandate I perform other functions in the forms, queries and reports I
have already set up so as not to compromise the integrity of the
newly updated database? If the answer is yes, if you could kindly
walk me thru the steps of the update required for the forms, queries
and reports.

If the PK you added had not been an AutoNumber then you would need to modify
your forms so that the PK field is populated when new records are added. Since
an AutoNumber is self-populating then you should not have to do anything.

 

[Vincent Johns]

I agree with Rick Brandt to some extent, and I can't think of any real
problems that you might encounter by adding Autonumber fields that you
never use, so you perhaps should go ahead and stick them in there (in
case you later need them) and nothing will go wrong, although they will
make your Tables (and thus the database file) slightly larger and
require you to name them and keep track of them.

However, the goal of a "properly designed database" is not, IMHO, a
sufficient reason all by itself for doing something. If you've been
given an assignment in class or by your boss to produce a "properly
designed database" according to some set of rules, then OK, you do what
your customer requires. Otherwise, it doesn't hurt to know the reasons
for making some choice.

In this case, one such reason for ensuring that all the records are
easily distinguishable (by having a unique primary key field in each
record) might be that the Table is part of a self-join. For example,
you might have a bunch of [Employee] records in which there's a field
indicating who the supervisor is, and the supervisor's record is also in
the [Employee] Table, so that the [Supervisor] field is a pointer back
to another record in the same Table.

In other cases, you may never define a Query which would make use of a
primary-key field in some Table, so such a field would just sit there
occupying a small amount of space and taking a small amount of extra
time when you add records to the Table. As I said in another message,
you can always add the Autonumber field later if you need it, but Rick
is probably correct that it won't hurt anything to add it now, even if
you never use it, and you won't have to worry about it later on if you
take care of that now. But suggesting that a Table that doesn't have a
primary key is somehow useless makes no sense to me, absent some
practical reason for putting that key there.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.