MS Access Vinyl Database 4 - Creating Relationships Between The Tables

In any database there will be certain relationships between the tables. For instance in our vinyl records database Elvis Presley released many records. Therefore there will be many data records in the t_music table that need to reference the data record for Elvis in the t_artist table.

Creating a relationship at database level is optional, but it will do three things:

1. It will save us from having to state the relationship in every single query we create
2. It will stop us from entering a music track with a reference to an artist who doesn't exist in the t_artist table
3. It will stop us from deleting an artist who has music tracks - otherwise that would leave what are called "orphan" records in the t_music table

To create our relationships, open the database and first of all click on Database Tools on the menu bar and then click the Relationships tool as shown.

The above screen window will open. We now need to select and add all of the tables shown. Either add them one at once, or click t_artist, then hold down the [Shift] key and click the bottom table. This will select them all and then clicking the Add button will add them all at once.

We now have a screen with all the tables added. They will all be the same height at first which means we will not be able to see all of the fields in the t_music table. To extend the t_music box click on the bottom border and with the mouse button still held down, drag the mouse downwards to expand the box as shown.

All of the relationships we are going to create involve the t_music table, so it makes sense to have that in the centre of the screen. You can move the tables about by clicking the title of the table and dragging.

First move the t_label table to a spot underneath the t_artist table and then move the t_music table into the space it has left in the middle.

Now to create our first relationship. We'll start with the t_label table and click and drag from the label_ID field upwards and to the right, releasing the mouse button whilst the mouse is hovering over the music_label_ID field of the t_music table. Make sure you do it in this direction and not by starting from the t_music table. Record labels release many music tracks. We don't want each music track to be able to have many labels...

The Edit Relationships dialogue box will open at this point. First of all, check that both label_ID and music_label_ID appear in the small table in the middle as shown. If all looks ok, click the Enforce Referential Integrity tick box. This is the factor that will stop you later from deleting a label with tracks existing in the t_music table. Otherwise by deleting the label you could leave the music tracks sort of floating in limbo and wondering which label released them... It will also stop you from adding a music track with a bad or non-existent reference to a label. For example; if you had 21 artists in the t_label table but tried to enter "47" as a label reference in the music_label_ID table. In a later article we'll create a form to enter the data in anyway and that will allow us to pick the label from a list of existing ones - you won't need to remember any artist_ID references and if you even try to enter a bad reference MS Access will tell you that you are being stupid... When you are certain everything looks right, click the Create Button.

Your screen should now look like this. A line appears between the two fields that have the relationship and it should show "1" against the t_label table and the sign for infinity "∞" against the t_music table.

In the same way, create relationships between the table t_media and t_music and finally between t_artist and t_music. Remember: always click and drag to the t_music table, not from it.

Our relationships are now in place. They should be treasured as should any good relationship!

Diposting oleh Abdullah Syafii