Data Modelling
Introduction
Data modelling in database design and object-oriented programming means the process whereby designers create a conceptual model of how the various data items in the software application relate to each other. This is where you decide how to structure your application data in multiple tables, and set the rules and relationships between the tables.
At the end of this process, you produce a design document that shows the tables and their relationships. The document is called a data model. Data modelling is therefore the activity and techniques involved in understanding the structure of an organisation, and proposing solution that enables the organisation to achieve its objectives. It bridges the gaps in between technical and functional areas.
Relationship in Data Modelling
A database that supports an application will need to use separate tables for different types of entities. Some examples are: customers, orders, items, messages, etc. Relationships exist between these tables. For example, customers can make orders, while orders contain various items. These relationships need to be represented in a database.
Let’s look at the following types of database relationships:
One to One Relationships
One to Many and Many to One Relationships
Many to Many Relationships
One to One Relationship
Let’s say we have one table containing a list of customers and another table containing a list of addresses, as follows:
If each address can only belong to one customer, the relationship between the two tables is “One to One”. Bear in mind that this kind of relationship is not common - we include this example for illustration purpose.
In the CUSTOMERS table, there is a column named “address_id”, which refers to the matching record in the ADDRESS table. The “address_id” in the CUSTOMERS table is called a “Foreign Key”, which is used in all kinds of database relationships.
Note also that the “customer_id” in the CUSTOMERS table is normally known as its “Primary Key” - the value here is unique for each row. Primary Key is used by database to identify all the records in a table. A primary key's main features are: It must contain a unique value for each row of data. It cannot contain null values. Similarly, the “address_id” in the ADDRESS table is the Primary Key for that table.
We can visualise the relationship between the CUSTOMER and ADDRESS tables as such:
One to Many Relationship and Many to One Relationship
Consider an online store where:
Customers can make many orders
Orders can contain many items
In these cases, we need to establish “One to Many” relationships. An example is as follows:
Each customer may have zero, one or multiple orders. But an order can belong to only one customer.
Many to Many Relationships There will be cases when you need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
To capture these relationships, we need to create an extra table
And this is how we visualise this kind of relationship:
Querying Multiple Tables in SQL
In the example here, we will look at how to use SQL to query and present information that is extracted from multiple tables with relationship.
First, we set up the two tables, customers and orders, with the following codes:
import sqlite3
conn = sqlite3.connect('order.db')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS customers')
cur.execute('CREATE TABLE customers (customer_id INTEGER
PRIMARY KEY, customer_name TEXT)')
cur.execute('DROP TABLE IF EXISTS orders')
cur.execute('''
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
customer_id INTEGER, amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES
customers(customer_id))
''')
conn.close()
Note above that in the orders table, we have set its column customer_id as a Foreign Key, and to make reference to the customer_id column in the customer table. The Foreign Key constraint will prevent invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
We now add some data to our database, using the codes below:
conn = sqlite3.connect('order.db')
cur = conn.cursor()
cur.execute('INSERT INTO customers (customer_id, customer_name)
VALUES (?, ?)',
(1, 'Adam'))
cur.execute('INSERT INTO customers (customer_id, customer_name)
VALUES (?, ?)',
(2, 'Andy'))
cur.execute('INSERT INTO customers (customer_id, customer_name)
VALUES (?, ?)',
(3, 'Joe'))
cur.execute('INSERT INTO customers (customer_id, customer_name)
VALUES (?, ?)',
(4, 'Sandy'))
cur.execute('INSERT INTO orders (order_id, customer_id, amount)
VALUES (?, ?, ?)',
(1, 1, 19.99))
cur.execute('INSERT INTO orders (order_id, customer_id, amount)
VALUES (?, ?, ?)',
(2, 1, 35.15))
cur.execute('INSERT INTO orders (order_id, customer_id, amount)
VALUES (?, ?, ?)',
(3, 3, 17.56))
cur.execute('INSERT INTO orders (order_id, customer_id, amount)
VALUES (?, ?, ?)',
(4, 4, 12.34))
conn.commit()
In the sample data here, we have 4 orders, 2 orders from customer_id 1, and one order each from customer_id 3 and 4 respectively. This is a typical One to Many type of relationship.
Say we want to display a list of customers together with details of their orders. In SQL, the concept is to “join” the two tables (customers and orders) and to display the relevant content. The SQL syntax to do this is:
SELECT * FROM customers JOIN orders WHERE
customers.customer_id =
orders.customer_id;If we just want to get the list of customer_name who has orders, then instead of “SELECT *” the syntax is:
SELECT customer_name from customers JOIN orders WHERE customers.customer_id = orders.customer_id;We can also add conditions to the query. For example, we might want to get just a list of customers with each order amount that exceeds 15. The SQL statement will be:.
SELECT * FROM customers JOIN orders WHERE
customers.customer_id =
orders.customer_id AND amount > 15;The code below shows how the SQL query statement is executed in Python:
conn = sqlite3.connect('order.db')
cur = conn.cursor()
cur.execute("SELECT * FROM customers JOIN orders \
WHERE customers.customer_id = orders.customer_id \
AND amount >15")
result = cur.fetchall()
conn.close()
for r in result:
print r