Creating the data model

A database is only useful when it has a clearly defined data model. This means that you have described how the data should be structured and added any constraints that are necessary. This structure is defined as an entity-relationship model and then implemented using the structured query language or SQL for short.

Coffee shop

Over the course of this tutorial you will develop a database to represent the order system of a small coffee shop. Imagine that you want customers to be able to order their drinks (and extras) without having to queue up - maybe they can build an order on their smartphone and then submit it whilst the sit surfing the web on their laptop at one of your benches.

We will come back to this scenario several times - so stick with it, it will make the rest of the resources easier to understand.

Entity-relationship model

To construct a data model for we must have an understanding of both the entities and relationships that exist in a given system. The entities that may exist in the coffee shop database include:

  • Product
  • Customer

In general you can think of an entity as an object or person in your system. This means that a single product or single customer is an entity. A group of products is an entity-type but most people refer to the grouping as an entity as well.

A single entity will have various attributes to represent its characteristics. Each attribute will have an attribute name (to describe the characteristic) and an attribute type (what kind of data it is).

Separate entities in the system may be related to one another. For example, a product may be purchased by customers - we can represent this as an entity-relationship diagram:

Product Customer ER Diagram

Notice the relationship between the entities has "crow's feet" at each end, this means:

  • a single product may be purchased by many customers
  • a single customer may purchase many products

We call this the degree of the relationship, in this instance the degree is many-to-many. There are four possible degrees of relationship:

Entity Degrees

Products

To begin with we will ignore relationships and focus on a single entity - product. There are usually a number of different types of coffee available, as well as teas, smoothies and other hot drinks such as hot chocolate.

There are lots of different pieces of data that we could store about each product but we will keep it simple for now. We can describe product using an entity description:

  • Product(ProductID,Name,Price)

The product name and price are obvious pieces of data to store about each product but why product id? This provides us with an obvious unqiue identifier for each product, making it easy to locate a particular product.

Now that we have a model of the entity we wish to create we need to consider how to implement it.

SQL

The structured query language is the standard way to interact with any database management system, so whilst we will be using SQLite the general principles apply to other systems as well. SQL can be separated into two parts:

  • Data Definition Language (DDL)
    • used to create the database structure
  • Data Manipulation Language (DML)
    • used to insert, delete, update, search and sort data in the database

We will use DDL to create the a representation of our product entity in our database.

Create table statement

To implement the product entity we must use the create table statement. Notice, that the statement is called create table and not create entity - this is because entity is used to refer to the abstract representation and table to refer to the actual implementation.

The full statement that is required to create the product table is as follows:

1
2
3
4
5
CREATE TABLE Product(
    ProductID integer,
    Name text,
    Price real,
    Primary Key(ProductID));

Notice, that you must define a data type for each attribute of product. There are other constraints that you can add to each attribute by we will leave them to one side for the moment.

Data types in SQLite3

Earlier it was mentioned that SQLite lacks some of the features of other systems. The one area where this is particularly noticeable is in data types. SQLite supports five data types:

  • NULL - used to represent 'nothing'
  • INTEGER
  • REAL - used to represent floating point numbers (decimals)
  • TEXT
  • BLOB - used to store binary data of any kind

This means that your attributes should have one of these five types - most of the time integer, real and text will be enough. If you are wondering how to store dates then the easiest way to do this is store them as text strings in ISO 8601 format:

  • YYYY-MM-DD HH:MM:SS

You can then use functions within Python to convert to and from actual date representations.

Creating the product table

The following video demonstrates how to add the product table to a database.


Task 1

Use the above video to help you create the product table.


Recreating the product table

Sometimes when you run your code you will want to recreate your database structure, other times you will want to use the existing structure. The SQL statement to drop a table is:

1
DROP TABLE Product

Obviously you do not want to run this statement by mistake! Therefore it is important to write your database structure code in such a way that enables you to easily delete tables when you want to but doesn't just do it automatically.

The video below demonstrates how to do this:


Task 2

Use the above video to improve the product create table statement.


Now that we have a table in our database we need to think about inserting data into that table.