Microsoft Access Database Design Concept - Step 4 Of 7: Table Relationships Of An Access Database

Once you have identified your tables, and have listed all the possible fields that will make up your tables including a 'common' field which is going to be the link to other tables forming the relational database (one of Microsoft Access database strengths), you are now ready to create your relational database.

The Table Design Process (including the Relationships)

So now you have a list of the tables, fields and their data types and in order to utilise Access as a true RDBMS (relational database management system), you will need to create the relationships between tables (and in some instances, queries too).

In order to join two tables together, there are rules that must be observed which are:

1. Field Data Types - the fields which are to be joined across two tables must be the same type of data. Therefore a 'Text' data type cannot join to a 'Number' type field.

The exception to the above is a 'Number' type can join to a 'AutoNumber' type providing both are set to same size (i.e. a Long Integer).

2. Field Sizes - applies to only the 'Text', 'Number'> and 'AutoNumber' data types where the size or length of a field can be modified. For the numeric types, the size must be the same and therefore a 'Number' type field size in one table which is set to 'Double' cannot join to a field whose size is set for example to 'Integer'.

The 'Text' data type is a little more relaxed as the joining field must be at least the same length (or greater) of characters (bytes)

Note: It is possible to join different field sizes with 'Number' data types provided the field can be cast and fit into the larger size. This is not good practice and in any event shouldn't the data type be the same across tables for the same data value?

3. Field Names? - this is not essential as field names are not checked when joining tables together. But for user-friendliness, having the same field name makes life just a little bit easier!

The success of creating a relationship is driven by the process of setting primary and foreign keys which indexes fields in a table.

This step simply is the principle to create a relational database and produces the database architecture required before moving onto the 'front-end' process.

The essential element is the mandatory primary key (the 'one' side of a relationship). The foreign key (sometimes referred as the 'secondary' key) is optional but will help with the speed of running queries if enabled and applied.

The type of key applied will lead to the type of Access relationship you can have and in essence there are four:

1. One-to-one
2. One-to-many
3. Many-to-one
4. Many-to-many (requires three tables)

The 'one' side is associated to a unique value for a field and is referred as the primary key. The 'many' side is an indexed field which allow for duplicate values in a field and is known as the foreign or secondary key.


access point vs router