One-to-One Relationships

<Back>

Click here to download the sample database used here

In Access “Relationships Design” view, drag each primary key
to the primary key in the other, related tables.

Click here for “Edit Relationships” dialog box

The “EmployeeID” primary key field is the thread to “join” data in three tables:

Notice that the “EmployeeID” primary key field in the “Employees Public Data” table

is connected to the “EmployeeID” primary key field in the “Employees Private Data”

as well as to the “EmployeeID” primary key field in the “Employees Photo Data.”

Connecting the primary keys defines a “one-to-one” relationship
because both ends of each relationship is a primary key,
and therefore there is really no “foreign” key.

The tables are connected by the primary keys,
and therefore each row in each table can have
no more than one matching row in any other table.

The “EmployeeID” primary key field in the “Employees Photo Data.”

The “EmployeeID” primary key field in the “Employees Photo Data.”

The “EmployeeID” primary key field in the “Employees Photo Data.”

(Notice that images are not displayed in datasheet view.
If there is an image, “Bitmap Image” is displayed.
If no image is stored, this field would be blank.
They are visible in the form view – see below.)

So, “EmployeeID” identified the row (record) in each table
that contains Fred Flintstone’s data.

The data may be spread across three tables,
seemingly in violation of database rules (“normalization”),
but the segregation is defensible for efficiency and privacy reasons :

Privacy Issues :

Private data is kept separate so that users who require the data in the “public” table
do not have access to the private data that is stored in a separate table.

Efficiency Issues :

Photos are large objects that take up a considerable amount of memory and disk storage
compared to text data that requires much less space and processor time to retrieve.

Therefore, photos are kept separate so that users who require the data in the “public” table
do not have to “waste” processor time retrieving large, slow photos
that they do no require. This saved a lot of processor time.

Photos are stored in a separate table and can be retrieved,
and only when and if they are needed.

Put it all together with a query and build a form on the query
to display all of the data from all three tables on one form :

Each frame on the form contains data from one of the tables in the one-to-one relationship.
Notice the primary key in each frame identifies its source table.

The employee’s name is contained in only the public table.

The other tables have nothing to connect them with the “main” (public) table
except for the primary key.

The primary key is the only common field between them all.

Click here to download the sample "1 to 1" database used here

<Back>