Relationships Between Records

 

One-to-one

One-to-many

Many-to-many

 

The following Tables represent a Music Company database. Table1 contains personal information about singers. Table2 contains records about songs. Table3 contains records about song publishers. Table4 contains records about singer payments and banking details. In each case, the Key Field is the first field.

 

Table1

 

Table2

 

Table3

 

Table4

*TaxNumber

 

*SongID

 

* SongTitle

 

*TaxNumber

FirstName

 

SongTitle

 

Publisher

 

FirstName

SURNAME

 

ReleaseDate

 

MusicalDirector

 

SURNAME

Telephone

 

SURNAME

 

SURNAME

 

RoyaltyDue

Address

 

 

 

 

 

BankAccount

 

There is only one record for each singer in Table1. A singer can have many songs. The relationship between Table1 and Table2 is “one-to-many”.

 

(If the publishing company allows more than one singer to release a song title, the relationship between Table1 and Table2 could be “many-to-many’.)

 

For any single song in Table2 there may be several publishers over a period of years. One record in Table2 may relate to many records in Table3. The relationship between Table2 and Table3 is “one-to-many”.

 

Similarly, the relationship between Table1 and Table2 is “one-to-many”. One singer could have many publishers.

 

 

As a general rule …

 

If Key Fields are ‘linked’, the relationship is “one-to-one”. If a Key Field links to a non-key field then the relationship is “one-to-many”.

 

Keep in mind that, by definition, a “Key Field” must be a unique identifier. No other record in the Table can have the same value.

 

Table1 and Table4 have a “one-to-one” relationship. It would be simpler to put all of the data into a single Table. This generally applies for all “one-to-one” relationships. In this case it has been separated for reasons of confidentiality.

 

<- Back