Primary Keys & Relationships

[naiveprogrammer]

What are the advantages of using a Primary Key exactly AND/OR Relationships?

I'm trying to keep a table updated (with duplicates) as the info is entered
via a form. The db is to keep track of different Companies that would be
recommended verses not recommended for whatever reason. Each Company has
it's own job # yet many different companies may have the same job # do to
working on the same project.

I really don't know where to go with this... I'm new to database design so
any help would be very much appreiciated!

Thank you!

 

[Tim Ferguson]

=?Utf-8?B?bmFpdmVwcm9ncmFtbWVy?=

What are the advantages of using a Primary Key exactly AND/OR
Relationships?

The last forty years of software database development is based on R
theory, which itself is based on set theory; and says that proper table
and relationship structure is the only way to guarantee the accuracy and
integrity of your data.

For further information, drop into any university bookshop and look for
titles about data structures, relational database design etc.

I really don't know where to go with this... I'm new to database
design so any help would be very much appreiciated!

This is probably not the place to shadow a complete design process --
this really includes the requirements analysis, data entities, entity
life history, and so on and so on. If what you really need is a quick-
and-dirty list manager, with the ability to sort, select, partition, and
search then it is very hard to beat something like MS Excel, and MS
Access certainly won't match it.

On the other hand, if you do want to find out about db design then you
have an exciting and (according to some sources!) rewarding learning
curve ahead of you. Hanging around here will certainly give you some
ideas for specific problems, but I guess you really need to start with a
class or at the least a well-informed mentor to guide you.

Hope that helps


Tim F

 

[Pat Hartman]

Primary keys are important because they provide the database engine with a
way to uniquely identify a row in a table. If it can't do this, it will not
let you delete a duplicate row from the table since it cannot identify
precisely which of the duplicate rows you are asking to delete. They are
also necessary when defining referential integrity since Access analyzes the
primary keys of the related tables to determine the cardinality (1-m, 1-1)
of the related tables. RI can only be enforced if Access (Jet) can
accurately determine the cardinality of the relationship. PKs may be
natural or artificial. Autonumbers are artificial. Keys that are made up
of 1 or more columns of data that uniquely identify a row are natural keys.
In your description it sounds like the combination of company and job would
be the natural candidate keys. Relationships are defined specifically to
enforce referential integrity. If you don't intend to enforce RI, there is
no need to define a relationship in an Access database. Once you have PKs
defined and create a relationship in which you check the enforce RI option,
you will be presented with the options to Cascade Delete and Cascade Update.
Cascade Delete is very useful. For example in hierarchical relationships
such as Order - OrderDetails, the OrderDetails rows have no meaning except
within the context of an Order so if you delete an Order, you would always
want to delete its child OrderDetails. Cascade delete will automatically
delete the OrderDetails rows when their parent Order is deleted. If you did
not enforce RI or did not choose Cascade Delete, you would need to take care
of deleting the OrderDetails yourself either with a query or a code loop.
Be careful to not choose Cascade Delete in other types of relationships.
For example, if you create a relationship between the Employee table and the
SexCode table, you don't want to delete all the male employees if someone
tries to delete the male sex code! In this case, since Cascade Delete is
not specified, RI will tell you that you cannot delete the record from the
SexCode table because there are related records in the Employee table. So
in this case RI protected you from an accident. I could go on, but you
really need to do some reading on the subject.

Access supports multi-field keys so if one field can not be used to uniquely
identify a row, you can choose up to 10 fields that in combination will
uniquely define a row. To create a multi-field key, select the first column
and while holding down the cntl key click and select up to nine more. When
all the necessary fields are selected, press the key icon on the toolbar.

Many people prefer to use autonumbers as their primary key when the natural
key of a table exceeds one column. If you choose to use an autonumber
primary key, you would then define a unique multi-field index to enforce the
business rule that each company can only be associated with a given job
number once. To create a multi-field unique index, open the index dialog
box. Enter a name for the index and choose the first column. Select unique
and ascending. then on the next line of the form, skip over the index name
field and just select the next column. Since the index name is empty,
Access will know that this column is part of the previously defined index.
Indexes support up to 10 columns.

 

[naiveprogrammer]

Wow! I can't say enough how wonderful ALL the info you have all contributed
is to me. Everything you all have said has really shed a lot of light on my
learning about PK & Relationships as well as a great foundation of learning
for Access.

Really Thanks guys!

 

[BruceM]

To Pat Hartman's thoughtful and thorough response I will add just a few
points. I think it helps if from the beginning you think of rows in a table
as records, and columns as fields. They look like rows and columns in a
spreadsheet, but they function very differently. A record is a collection of
fields. When you look at a table it is sorted by some field or other, which
you can change if it is more convenient, but the order in which they appear
has no effect on the way the database performs. If the records need to be in
order (by name or whatever) you need to use a query (or a sort order in a
report). Working directly with tables is for the database
designer/administrator only, not for the users.
There is some disagreement about natural primary keys vs. autonumbers, but I
will say that if you are just getting started an autonumber is a good way to
go. Remember that an autonumber will almost inevitably have gaps in the
sequence. If you move to a new record it is assigned a new autonumber
primary key. If you move to the previous record, then forward again, the new
record will be assigned a different autonumber than it was the first time.
An autonumber is not for you to use as an invoice number or anything like
that, since you cannot really control it. It is for the database's purposes
only. You will use it to establish relationships, but you should not be
concerned about what the actual number is.