How to Map a Data Model to a Database

This post will examine how to map a data model to a database.

For a basic data model, decisions are made on whether to classify objects as “things” or “attributes“. Consider an application built for managing audio tracks.

Data Model

To map the data model from a logic model to a physical model, a database, you have to augment your table. Foreign keys are used to augment a table. Each foreign key points to a primary key of another table.

Primary Keys, Foreign Keys, Logical Keys

For an application that manages audio tracks, the albums should be numbered. These numbers would be called the primary keys or the “id“. For an album numbered 4, then that primary key “id” would be listed as the foreign key “album_id” for a track that is on that album.

Primary keys become end points that a foreign key would point to. The logical key is something you might use in an SQL WHERE clause.

wk3b_representing_2

It is important to have a naming convention for any data model. It makes drawing out the data model much easier.

Data Model Sketch

 

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.