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

 

Consider SQLite with Python to build a database. These can both run on the desktop, are fast, and easy to setup.

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.

Using Databases with Python – A Must Know

It is a good idea to understand the importance of using databases with Python while learning Python. If you are analyzing data, pulling data from over the network, then it makes sense to store that data in a database. You can then set up a process of pulling data from the database as you need it. Overall, it can speed up your workflow.

A good database system to learn using databases with Python is DB Browser for SQLite.

using databases with python

Relational databases comprise a whole sub-field of computer science. They are relevant because you can pull out an entry from huge amounts of data in a split-second. It would take you much longer if you had to read through the data.

relational databases

You can look no further than Oracle to understand the relevance of relational databases. The majority of their revenue comes from database products.

The underlying foundation of databases is rooted in mathematics. This is present in the terminology that experts use to describe databases.

database terminology

The idea behind databases is that you model data at a connection point.

database model

However, programmers tend to think of it in terms of rows and columns.

Typically, when you make a table, the first row becomes metadata for the table. You often use the first row to title what each column is for. Therefore, you can refer to this first row as the schema for the table. It sets the rules for each column with regard to what goes there, for example, a string, an integer, etc.

In the early 1960s, the database pioneers figured out ways to quickly retrieve data from random access memory, without having to go through the data sequentially. However, databases were very complex. As a result, a new component of internet architecture evolved called the database application. This allows you, the programmer, to talk with the database, by way of the database application. At this point, an industry standard was desired for the language for the API between a database and its application. The name of the language the industry agreed on was SQL (Structured Query Language).

metadata

SQL is a great language, but it depends on the data being clean . The nice thing about Python, is it can deal really well with unstructured data. So together, Python and SQL, you have a powerful combination.

SQL