Relationships

Tables inside a database are linked together on key fields. For instance, if I was looking at a sales table, which contained a customer reference field, and I wanted to see the name of the customer, I could follow the relationship from the customer reference to the customer table. Due to the relationship between these two tables, the customer table should be sitting on the correct record for that particular customer. I can then find out all the data held about that customer in that record in the customer table.

A foreign key is not the primary key of its own table, but it is related to a primary key in another table. When relations are set between two fields from separate tables, the 2 fields must be the same data type and size.

Usually relationships are of the variety one to many, where a key field or fields are linked to a foreign key in the other table. This is called one to many, since each record in one table is linked to one or more records in another table.

You can however have relationships between tables that are many to many, where each record in the one table is related to one or more records in another table. However, unlike in a one to many relationship, in a many to many relationship, the field which is linked can also occur more than once in the first table. This type of relationship is rare and is usually deposed of at the design stage of producing a database system.

You can also have a relationship called one to one, where each record in a table is linked to only one other record in another table. This however is rare and if it occurs the two tables should usually be combined into one.