Database Design for Linking Tables

Imagine building an application that manages audio tracks. For this type of application, database design should be one of the first steps in its design. Actually, good database design is crucial to most applications.

database design

In database design for applications, tables are made for the data, and they indicate the relationships between tables. Hence the term “relational database“.

A picture is often drawn to show how the data will be stored. This picture becomes the schema, or contract, for the database.

design sketch

The connections between data are what makes the database powerful. A well planned database can enable your application to run very quickly.

The basic rule of building a data model is to not replicate data. Rather, use a relationship.

building a data model

Now, consider building an application that manages audio tracks. Consider what the user interface may look like. It it is very important to build an interface that the user wants. An efficient data model is then used to construct the application.

Replication of data in columns will slow down the performance of a database as it scales up. The picture below shows a great user interface, but for a single table, the replication of some column data would eventually slowdown the application.

interface

To solve the problem of data replication, separate tables can be made that relate to each other. The tables can then be joined to give an interface the user wants. When deciding what separate tables to create, the idea is to look at data and classify it either as its own “thing” or an “attribute” of a thing.

Decide on the “thing” that is most central to the application. In the case of an application to manage audio tracks, the central “thing” are tracks.

The first table to build is a track table. The track title, length, rating, and number of times played are all attributes of the track. Thus, the track table would have four columns: title, length, rating, and play count.

thing or attribute

In the above picture there is replicated data for artist, album, and genre. The fact that data is replicated is an indicator of it not being an “attribute” of tracks, but rather it’s its own “thing” worthy of another table.

Thus, make a table for albums, and tracks would be related to an album. Then make a table for artist, and each album would be related to an artist.

Finally, decide where genre connects to. If genre is connected to album or artist, then every time a change is made for genre of a track, the genre will change for every track of that album or artist. To prevent this, it’s best to connect genre to tracks.

Hopefully by now it’s easy to see that a well designed relational database model can take quite a bit of time. However, the tradeoff is that it can give an application speed and power.

Leave a Reply

Your email address will not be published. Required fields are marked *