So far in this book, all the work we've done has been with a single database table. The majority of databases you'll work with as a developer will have more than one table, and those tables will be connected together in various ways to form table relationships. In this chapter we'll explore the reasons for having multiple tables in a database, look at how to define relationships between different tables, and outline the different types of table relationships that can exist.
At this point, our
users table doesn't need to hold that much data for each user in our system. It stores a name for the user, whether their account is enabled or not, when they last logged in, and an id to identify each user record. In reality, the requirements of our application will mean that we need to store a lot more data than that. Our app will be used to manage a library of SQL books and allow users to checkout the books and also review them.
To implement some of these requirements we could simply try adding more columns to our
users table; the resulting table might look a little like this:
Wow, that's a lot of information all in one table! There are other issues here as well, such as duplication of data (often referred to as 'redundancy'). For each book that a user checks out, we have to repeat all of the user data in our table. It's a similar story with the book data, if more than one person checks out a book, such as with 'My Second SQL Book', we have to repeat the book title, author, isbn, and published date.
Duplicating data in this way can lead to issues with data integrity. For example what if for one of the 'My Second SQL Book' checkouts the title is entered as 'My 2nd SQL Book' instead, or a typo had been made with the isbn on one of the rows? From looking at the data in the table, how would we know which piece of data is correct?
How do we deal with this situation? The answer is to split our data up across multiple different tables, and create relationships between them. The process of splitting up data in this way to remove duplication and improve data integrity is known as normalization.
Normalization is a deep topic, and there are complex sets of rules which dictate the extent to which a database is judged to be normalized. These rule-sets, known as 'normal forms', are beyond the scope of this book; for now there are two important things to remember:
So we know that we want to split the data for our application across multiple tables, but how do we decide what those tables should be and what relationships should exist between them? When answering questions such as these it is often useful to zoom out and think at a higher level of abstraction, and this is where the process of database design comes in.
At a high level, the process of database design involves defining entities to represent different sorts of data and designing relationships between those entities. But what do we mean by entities, and how do different entities relate to each other? Let's find out.
An entity represents a real world object, or a set of data that we want to model within our database; we can often identify these as the major nouns of the system we're modeling. For the purposes of this book we're going to try and keep things simple and draw a direct correlation between a entity and a single table of data; in a real database however, the data for a single entity might be stored in more than one table.
What entities might we define for our SQL Book application?
userstable, and we can think of a user as a specific entity within our app; a 'user' is someone who uses our app.
Now we have defined the entities we need, we can plan tables to store the data for each entity. Those tables might look something like this:
We're making good progress with our database design. We've decided on the entities we want and have formed a picture of the tables we need, the columns in those tables, and even examples of the data that those columns will hold. There's something missing though, and that's the relationships between our entities.
If we look at the diagram of our five tables, the tables are all isolated and it's not obvious how these tables should relate to each other. Let's simplify our tables a bit and explicitly define some relationships between them.
This diagram shows an abstract representation of our various entities and also the relationships between them. We can think of this diagram as a simple Entity Relationship Diagram, or ERD. An ERD is a graphical representation of entities and their relationships to each other, and is a commonly used tool within database design.
There are different types of ERD varying from conceptual to detailed, and often using specific conventions such as crow's foot notation to model relationships. We won't go into the details of these different types, or the conventions they use, in this book. For now it's useful to simply think of an ERD as any diagram which models relationships between entities.
Okay, so we now know the tables that we need and we've also defined the relationships that should exist between those tables in our ERD, but how do we actually implement those relationships in terms of our table schema? The answer to that is to use keys.
In an earlier section of this book we looked at an aspect of schema called constraints, and explored how constraints act on and work with the data in our database tables. Keys are a special type of constraint used to establish relationships and uniqueness. They can be used to identify a specific row in the current table, or to refer to a specific row in another table. In this chapter we'll look at two types of keys that fulfil these particular roles: Primary Keys, and Foreign Keys.
A necessary part of establishing relationships between two entities or two pieces of data is being able to identify the data correctly. In SQL, uniquely identifying data is critical. A Primary Key is a unique identifier for a row of data.
In order to act as a unique identifier, a column must contain some data, and that data should be unique to each row. If you're thinking that those requirements sound a lot like our
NOT NULL and
UNIQUE constraints, you'd be right; in fact, making a column a
PRIMARY KEY is essentially equivalent to adding
NOT NULL and
UNIQUE constraints to that column.
id column in our
users table has both of these constraints, and we've used that column in many of our
SELECT queries in order to uniquely identify rows; so we've effectively had
id as a primary key all along although we haven't explicitly set it as the Primary Key. Let's do that now:
ALTER TABLE users ADD PRIMARY KEY (id);
Although any column in a table can have
NOT NULL constraints applied to them, each table can have only one Primary Key. It is common practice for that Primary Key to be a column named
id. If you look at the other tables we've defined for our database, most of them have an
id column. While a column of any name can serve as the primary key, using a column named
id is useful for mnemonic reasons and so is a popular convention.
Being able to uniquely identify a row of data in a table via that table's Primary Key column is only half the story when it comes creating relationships between tables. The other half of this story is the Primary Key's partner, the Foreign Key.
A Foreign Key allow us to associate a row in one table to a row in another table. This is done by setting a column in one table as a Foreign Key and having that column reference another table's Primary Key column. Creating this relationship is done using the
REFERENCES keyword in this form:
FOREIGN KEY (fk_col_name) REFERENCES target_table_name (pk_col_name)
We'll explore some specific examples of how this is used when we look at setting up various kinds of relationships later in this chapter, but in general terms you can think of this reference as creating a connection between rows in different tables.
Imagine for instance that we have two tables, one called
colors and one called
color_id column of the
shapes table is a Foreign Key which references the
id column of the
In diagram above, the 'Red' row of our
colors table is associated with the 'Square' and 'Star' rows of our
shapes table. Similarly, 'Blue' is associated with 'Triangle' and 'Green' with 'Circle'. 'Orange' isn't currently associated with any row in the
shapes table, but there is the potential to create such an association if we insert a row into
shapes with a
By setting up this reference, we're ensuring the referential integrity of a relationship. Referential integrity is the assurance that a column value within a record must reference an existing value; if it doesn't then an error is thrown. In other words, PostgreSQL won't allow you to add a value to the Foreign Key column of a table if the Primary Key column of the table it is referencing does not already contain that value. We'll discuss this concept in a bit more detail later on.
The specific way in which a Foreign Key is used as part of a table's schema depends on the type of relationship we want to define between our tables. In order to implement that schema correctly it is useful to formally describe the relationships we need to model between our entities:
The entity relationships described above can be classified into three relationship types:
Let's look at them each in turn.
A one-to-one relationship between two entities exists when a particular entity instance exists in one table, and it can have only one associated entity instance in another table.
Example: A user can have only one address, and an address belongs to only one user.
In the database world, this sort of relationship is implemented like this: the
id that is the
PRIMARY KEY of the
users table is used as both the
FOREIGN KEY and
PRIMARY KEY of the
/* one to one: User has one address */ CREATE TABLE addresses ( user_id int, -- Both a primary and foreign key street varchar(30) NOT NULL, city varchar(30) NOT NULL, state varchar(30) NOT NULL, PRIMARY KEY (user_id), FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE );
Executing the above SQL statement will create an
addresses table, and create a relationship between it and the
users table. Notice the
PRIMARY KEY and
FOREIGN KEY clauses at the end of the
CREATE statement. These two clauses create the constraints that makes the
user_id the Primary Key of the
addresses table and also the Foreign Key for the
Let's go ahead and add some data to our table.
INSERT INTO addresses (user_id, street, city, state) VALUES (1, '1 Market Street', 'San Francisco', 'CA'), (2, '2 Elm Street', 'San Francisco', 'CA'), (3, '3 Main Street', 'Boston', 'MA');
user_id column uses values that exist in the
id column of the
users table in order to connect the tables through the foreign key constraint we just created.
We're going to take a slight detour here to discuss a topic that's extremely important when dealing with table relationships: referential integrity. This is a concept used when discussing relational data which states that table relationships must always be consistent. Different RDBMSes might enforce referential integrity rules differently, but the concept is the same.
The constraints we've defined for our
addresses table enforce the one to one relationship we want between it and our
users table, whereby a user can only have one address and an address must have one, and only one, user. This is an example of referential integrity. Let's demonstrate how this works.
What happens if we try to add another address for a user who already has one?
INSERT INTO addresses (user_id, street, city, state) VALUES (1, '2 Park Road', 'San Francisco', 'CA');
ERROR: duplicate key value violates unique constraint "addresses_pkey" DETAIL: Key (user_id)=(1) already exists.
The error above occurs because we are trying to insert a value
1 into the
user_id column when such a value already exists in that column. The
UNIQUE constraint on the column prevents us from doing so.
How about if we try to add an address for a user who doesn't exist?
INSERT INTO addresses (user_id, street, city, state) VALUES (7, '11 Station Road', 'Portland', 'OR');
ERROR: insert or update on table "addresses" violates foreign key constraint "addresses_user_id_fkey" DETAIL: Key (user_id)=(7) is not present in table "users".
Here we get a different error. The
FOREIGN KEY constraint on the
user_id column prevents us from adding the value
7 to that column because that value is not present in the
id column of the
If you're wondering why we can add a user without an address but can't add an address without a user, this is down to the modality of the relationship between the two entities. Don't worry about exactly what this means for now, just think of it as another aspect of entity relationships.
You might have noticed in the table creation statement for our
addresses table, the
FOREIGN KEY definition included a clause which read
ON DELETE CASCADE. Adding this clause, and setting it to
CASCADE basically means that if row being referenced is deleted, the row referencing it is also deleted. There are alternatives to
CASCADE such as
SET NULL or
SET DEFAULT which instead of deleting the referencing row will set a new value in the appropriate column for that row.
Determining what to do in situations where you delete a row that is referenced by another row is an important design decision, and is part of the concept of maintaining referential integrity. If we don't set such clauses we leave the decision of what to do up to the RDBMS we are using. In the case of PostgreSQL, if we try to delete a row that is being referenced by a row in another table and we have no
ON DELETE clause for that reference, then an error will be thrown.
Okay, time to get back to our different table relationship types with a look at one-to-many. A one-to-many relationship exists between two entities if an entity instance in one of the tables can be associated with multiple records (entity instances) in the other table. The opposite relationship does not exist; that is, each entity instance in the second table can only be associated with one entity instance in the first table.
Example: A book has many reviews. A review belongs to only one book.
Let's set up the necessary data. First let's create our tables
CREATE TABLE books ( id serial, title varchar(100) NOT NULL, author varchar(100) NOT NULL, published_date timestamp NOT NULL, isbn char(12), PRIMARY KEY (id), UNIQUE (isbn) ); /* one to many: Book has many reviews */ CREATE TABLE reviews ( id serial, book_id integer NOT NULL, reviewer_name varchar(255), content varchar(255), rating integer, published_date timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE );
These table creation statements for our
reviews tables are fairly similar to our previous example. There's a key difference worth pointing out in the statement for our
reviews table however:
FOREIGN KEYreference different columns,
book_idrespectively. This means that the
book_idis not bound by the
UNIQUEconstraint of our
PRIMARY KEYand so the same value from the
idcolumn of the
bookstable can appear in this column more than once. In other words a book can have many reviews.
Now we have created our
reviews tables, let's add some data to them.
INSERT INTO books (id, title, author, published_date, isbn) VALUES (1, 'My First SQL Book', 'Mary Parker', '2012-02-22 12:08:17.320053-03', '981483029127'), (2, 'My Second SQL Book', 'John Mayer', '1972-07-03 09:22:45.050088-07', '857300923713'), (3, 'My First SQL Book', 'Cary Flint', '2015-10-18 14:05:44.547516-07', '523120967812'); INSERT INTO reviews (id, book_id, reviewer_name, content, rating, published_date) VALUES (1, 1, 'John Smith', 'My first review', 4, '2017-12-10 05:50:11.127281-02'), (2, 2, 'John Smith', 'My second review', 5, '2017-10-13 15:05:12.673382-05'), (3, 2, 'Alice Walker', 'Another review', 1, '2017-10-22 23:47:10.407569-07');
The order in which we add the data is important here. Since a column in
reviews references data in
books we must first ensure that the data exists in the
books table for us to reference.
Just as with the
addresses relationship, the
FOREIGN KEY references creates relationships between the
reviews table and the
books table. Unlike that
addresses relationship however, both books and users can have multiple review. For example the
id value of
My Second SQL Book appears twice in the
book_id column of the
In a real database our
reviews table would probably also have a Foreign Key reference to the
id column in
users table rather than have user type data directly in a
reviewer_name column. We set up the table in this way for our example because we wanted to focus on the one-to-many relationship type. If we had added such a Foreign Key to
reviews we'd effectively be setting up a Many-to-Many relationship between
users, which is what we'll look at next.
A many-to-many relationship exists between two entities if for one entity instance there may be multiple records in the other table, and vice versa.
Example: A user can check out many books. A book can be checked out by many users (over time).
In order to implement this sort of relationship we need to introduce a third, cross-reference, table. This table holds the relationship between the two entities, by having two
FOREIGN KEYs, each of which references the PRIMARY KEY of one of the tables for which we want to create this relationship. We already have our
users tables, so we just need to create the cross-reference table:
user_id column in
checkouts references the
id column in
users, and the
book_id column in
checkouts references the
id column in
books. Each row of the
checkouts table uses these two Foreign Keys to create an association between rows of
We can can see on the first row of
checkouts, the user with an
1 is associated with the book with an
1. On the second row, the same user is also associated with the book with an
2. On the third row a different user, with and
2, is associated with the same book from the previous row. On the fourth row, the user with an
5 is associated with the book with an
Don't worry if you don't completely understand this right away, we'll take a look shortly at what these associations look like in terms of the data in
books. First, let's create our
checkouts table and add some data to it.
CREATE TABLE checkouts ( id serial, user_id int NOT NULL, book_id int NOT NULL, checkout_date timestamp, return_date timestamp, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE );
You may have noticed that our table contains a couple of other columns
return_date. While these aren't necessary to create the relationship between the
books table, they can provide additional context to that relationship. Attributes like a checkout date or return date don't pertain specifically to users or specifically to books, but to the association between a user and a book.
This kind of additional context can be useful within the business logic of the application using our database. For example, in order to prevent more than one user trying to check out the same book at the same time, the app could could determine which books are currently checked out by querying those that have a value in the
checkout_date column of the
checkouts table but where the
return_date is set to
Now that we have our
checkouts created, we can add the data that will create the associations between the rows in
INSERT INTO checkouts (id, user_id, book_id, checkout_date, return_date) VALUES (1, 1, 1, '2017-10-15 14:43:18.095143-07', NULL), (2, 1, 2, '2017-10-05 16:22:44.593188-07', '2017-10-13 13:0:12.673382-05'), (3, 2, 2, '2017-10-15 11:11:24.994973-07', '2017-10-22 17:47:10.407569-07'), (4, 5, 3, '2017-10-15 09:27:07.215217-07', NULL);
Let's have a look at what this data looks like in terms of the relationships between the tables.
Here we can see that the
id value of
1 from the
users table for 'John Smith' appears twice in the
user_id column of
checkouts, but alongside different values for
2); so this satisfies the 'a user can check out many books' part of the relationship. Similarly we can see that
id value of
2 from the
books table for 'My Second SQL Book' appears twice in the
books_id column of
checkouts, alongside different values for
2); so this satisfies the 'a book can be checked out by many users' part of the relationship.
We can perhaps think of a Many-to-Many relationship as combining two One-to-One relationships; in this case between
users, and between
In this chapter we covered a number of different topics regarding table relationships:
To recap, here is a list of common relationships that you'll encounter when working with SQL:
|One-to-One||A User has ONE address|
|One-to-Many||A Book has MANY reviews|
|Many-to-Many||A user has MANY books and a book has MANY users|
Earlier in this book we looked at how to query data in a database table using
SELECT. Now that our data is split across multiple tables, how can we structure our queries if we need data from more than one table at the same time? In order to do this, we need to join our tables together. In the next chapter we'll explore how to do exactly that by introducing another SQL keyword,
Make sure you are connected to the
encyclopedia database. We want to hold the continent data in a separate table from the country data.
continentstable with an auto-incrementing
idcolumn (set is as the Primary Key), and a
continent_namecolumn which can hold the same data as the
continentcolumn from the
continentcolumn from the
continent_idcolumn to the
countriestable of type integer.
idfield of the
The form for adding a Foreign Key to an existing table is:
ALTER TABLE countries ADD FOREIGN KEY (column_name) REFERENCES other_table(other_table_primary_key);
encyclopedia=# CREATE TABLE continents ( encyclopedia(# id serial PRIMARY KEY, encyclopedia(# continent_name varchar(50) encyclopedia(# ); CREATE TABLE encyclopedia=# ALTER TABLE countries encyclopedia-# DROP COLUMN continent; ALTER TABLE encyclopedia=# ALTER TABLE countries encyclopedia-# ADD COLUMN continent_id integer; ALTER TABLE encyclopedia=# ALTER TABLE countries encyclopedia-# ADD FOREIGN KEY (continent_id) encyclopedia-# REFERENCES continents(id); ALTER TABLE
Write queries to add data to the
continents tables so that the data below is correctly represented across the two tables. Add both the countries and the continents to their respective tables in alphabetical order.
|USA||Washington D.C.||325,365,189||North America|
We first need to add the continent names to the
continents table, since we will be referencing the
id value for each entry in
encyclopedia=# INSERT INTO continents (continent_name) VALUES encyclopedia-# ('Africa'), encyclopedia-# ('Asia'), encyclopedia-# ('Europe'), encyclopedia-# ('North America'), encyclopedia-# ('South America'); INSERT 0 5
Once we have added this data, we can write a simple
SELECT query to verify the
id values for each continent.
encyclopedia=# SELECT * FROM continents; id | continent_name ----+---------------- 1 | Africa 2 | Asia 3 | Europe 4 | North America 5 | South America (5 rows)
We can then use these
id values from the
continents table as the Foreign Key values for the
continent_id column in the
INSERT statement for our
encyclopedia=# INSERT INTO countries (name, capital, population, continent_id) encyclopedia-# VALUES ('Brazil', 'Brasilia', 208385000, 5), encyclopedia-# ('Egypt', 'Cairo', 96308900, 1), encyclopedia-# ('France', 'Paris', 67158000, 3), encyclopedia-# ('Germany', 'Berlin', 82349400, 3), encyclopedia-# ('Japan', 'Tokyo', 126672000, 2), encyclopedia-# ('USA', 'Washington D.C.', 325365189, 4); INSERT 0 6
Examine the data below:
|Album Name||Released||Genre||Label||Singer Name|
|Born to Run||August 25, 1975||Rock and roll||Columbia||Bruce Springsteen|
|Purple Rain||June 25, 1984||Pop, R&B, Rock||Warner Bros||Prince|
|Born in the USA||June 4, 1984||Rock and roll, pop||Columbia||Bruce Springsteen|
|Madonna||July 27, 1983||Dance-pop, post-disco||Warner Bros||Madonna|
|True Blue||June 30, 1986||Dance-pop, Pop||Warner Bros||Madonna|
|Elvis||October 19, 1956||Rock and roll, Rhythm and Blues||RCA Victor||Elvis Presley|
|Sign o' the Times||March 30, 1987||Pop, R&B, Rock, Funk||Paisley Park, Warner Bros||Prince|
|G.I. Blues||October 1, 1960||Rock and roll, Pop||RCA Victor||Elvis Presley|
We want to create an
albums table to hold all the above data except the singer name, and create a reference from the
albums table to the
singers table to link the each album to the correct singer. Write the necessary SQL statements to do this and to populate the table with data. Assume Album Name, Genre, and Label can hold strings up to 100 characters. Include an auto-incrementing
id column in the
The column of the
singers table used as a the Foreign Key for the
albums table will need to have a
First we need to ensure that the
id column of the
singers table has a
encyclopedia=# ALTER TABLE singers encyclopedia-# ADD CONSTRAINT unique_id UNIQUE (id); ALTER TABLE
Then was can create an
albums table that references that column.
encyclopedia=# CREATE TABLE albums ( encyclopedia(# id serial PRIMARY KEY, encyclopedia(# album_name varchar(100), encyclopedia(# released date, encyclopedia(# genre varchar(100), encyclopedia(# label varchar(100), encyclopedia(# singer_id int, encyclopedia(# FOREIGN KEY (singer_id) REFERENCES singers(id) encyclopedia(# ); CREATE TABLE
Now we can populate the table with the necessary data, using the appropriate
id values from the
singers table for
singer_id of the
albums table (you can run a
SELECT query on the
singers table to get these values).
encyclopedia=# INSERT INTO albums (album_name, released, genre, label, singer_id) encyclopedia-# VALUES ('Born to Run', '1975-08-25', 'Rock and roll', 'Columbia', 1), encyclopedia-# ('Purple Rain', '1984-06-25', 'Pop, R&B, Rock', 'Warner Bros', 6), encyclopedia-# ('Born in the USA', '1984-06-04', 'Rock and roll, pop', 'Columbia', 1), encyclopedia-# ('Madonna', '1983-07-27', 'Dance-pop, post-disco', 'Warner Bros', 5), encyclopedia-# ('True Blue', '1986-06-30', 'Dance-pop, Pop', 'Warner Bros', 5), encyclopedia-# ('Elvis', '1956-10-19', 'Rock and roll, Rhythm and Blues', 'RCA Victor', 7), encyclopedia-# ('Sign o'' the Times', '1987-03-30', 'Pop, R&B, Rock, Funk', 'Paisley Park, Warner Bros', 6), encyclopedia-# ('G.I. Blues', '1960-10-01', 'Rock and roll, Pop', 'RCA Victor', 7); INSERT 0 8
Connect to the
ls_burger database. If you run a simple
SELECT query to retreive all the data from the
orders table, you will see it is very unnormalised. We have repetition of item names and costs and of customer data.
ls_burger=# SELECT * FROM orders; id | customer_name | burger | side | drink | customer_email | customer_loyalty_points | burger_cost | side_cost | drink_cost ----+----------------+-------------------------+-------------+-----------------+-------------------------+-------------------------+-------------+-----------+------------ 3 | Natasha O'Shea | LS Double Deluxe Burger | Onion Rings | Chocolate Shake | email@example.com | 42 | 6.00 | 1.50 | 2.00 2 | Natasha O'Shea | LS Cheeseburger | Fries | | firstname.lastname@example.org | 18 | 3.50 | 1.20 | 0.00 1 | James Bergman | LS Chicken Burger | Fries | Lemonade | email@example.com | 28 | 4.50 | 1.20 | 1.50 4 | Aaron Muller | LS Burger | Fries | | | 13 | 3.00 | 1.20 | 0.00 (4 rows)
We want to break this table up into multiple tables. First of all create a
customers table to hold the customer name data and an
email_addresses table to hold the customer email data. Create a one-to-one relationship between them, ensuring that if a customer record is deleted so is the equivalent email address record. Populate the tables with the appropriate data from the current
First let's create the
customers table. This just needs an
id field and a
customer_name field. The
id field should be a
PRIMARY KEY so that it is
ls_burger=# CREATE TABLE customers ( ls_burger(# id serial PRIMARY KEY, ls_burger(# customer_name varchar(100) ls_burger(# ); CREATE TABLE
Next we need to create our
email_addresses table. Since, in our system, a customer should only have one email address, and an email address should only belong to one customer, we need to create a one-to-one relationship. We do that by having a
customer_id field in the table that acts as both the
PRIMARY KEY for the
email_addresses and the
FOREIGN KEY for the
customers table. We add the
ON DELETE clause set to
CASCADE to ensure that if we delete a customer, the email address record is also deleted.
ls_burger=# CREATE TABLE email_addresses ( ls_burger(# customer_id integer PRIMARY KEY, ls_burger(# customer_email varchar(50), ls_burger(# FOREIGN KEY (customer_id) ls_burger(# REFERENCES customers (id) ls_burger(# ON DELETE CASCADE ls_burger(# ); CREATE TABLE
Finally we can populate both our new tables with data.
ls_burger=# INSERT INTO customers (customer_name) ls_burger-# VALUES ('James Bergman'), ls_burger-# ('Natasha O''Shea'), ls_burger-# ('Aaron Muller'); INSERT 0 3 ls_burger=# INSERT INTO email_addresses (customer_id, customer_email) ls_burger-# VALUES (1, 'firstname.lastname@example.org'), ls_burger-# (2, 'email@example.com'); INSERT 0 2
We want to make our ordering system more flexible, so that customers can order any combination of burgers, sides and drinks. The first step towards doing this is to put all our product data into a separate table called
products. Create a table and populate it with the following data:
|Product Name||Product Cost||Product Type||Product Loyalty Points|
|LS Chicken Burger||4.50||Burger||20|
|LS Double Deluxe Burger||6.00||Burger||30|
The table should also have an auto-incrementing
id column which acts as its
PRIMARY KEY. The
product_type column should hold strings of up to 20 characters. Other than that, the column types should be the same as their equivalent columns from the
First we create the table.
ls_burger=# CREATE TABLE products ( id serial PRIMARY KEY, product_name varchar(50), product_cost numeric(4,2) DEFAULT 0, product_type varchar(20), product_loyalty_points integer ); CREATE TABLE
Then we can add the data to it.
ls_burger=# INSERT INTO products (product_name, product_cost, product_type, product_loyalty_points) ls_burger-# VALUES ('LS Burger', 3.00, 'Burger', 10 ), ls_burger-# ('LS Cheeseburger', 3.50, 'Burger', 15 ), ls_burger-# ('LS Chicken Burger', 4.50, 'Burger', 20 ), ls_burger-# ('LS Double Deluxe Burger', 6.00, 'Burger', 30 ), ls_burger-# ('Fries', 1.20, 'Side', 3 ), ls_burger-# ('Onion Rings', 1.50, 'Side', 5 ), ls_burger-# ('Cola', 1.50, 'Drink', 5 ), ls_burger-# ('Lemonade', 1.50, 'Drink', 5 ), ls_burger-# ('Vanilla Shake', 2.00, 'Drink', 7 ), ls_burger-# ('Chocolate Shake', 2.00, 'Drink', 7 ), ls_burger-# ('Strawberry Shake', 2.00, 'Drink', 7); INSERT 0 11
To associate customers with products, we need to do two more things:
orderstable so that we can associate a customer with one or more orders (we also want to record an order status in this table).
order_itemstable so that an order can have one or more products associated with it.
Based on the order descriptions below, amend and create the tables as necessary and populate them with the appropriate data.
James has one order, consisting of a Chicken Burger, Fries, Onion Rings, and a Lemonade. It has a status of 'In Progress'.
Natasha has two orders. The first consists of a Cheeseburger, Fries, and a Cola, and has a status of 'Placed'; the second consists of a Double Deluxe Burger, a Cheeseburger, two sets of Fries, Onion Rings, a Chocolate Shake and a Vanilla Shake, and has a status of 'Complete'.
Aaron has one order, consisting of an LS Burger and Fries. It has a status of 'Placed'.
Assume that the
order_status field of the
orders table can hold strings of up to 20 characters.
Since we are going to amend the
orders table so drastically, one option would be to drop it and then recreate it. The orders table needs to reference the
customers table but also be referenced by the
order_items table, so we need an
id field, to uniquely identify each order, a
customer_id field to reference the customer who placed the order, and an
ls_burger=# DROP TABLE orders; DROP TABLE ls_burger=# CREATE TABLE orders ( ls_burger(# id serial PRIMARY KEY, ls_burger(# customer_id integer, ls_burger(# order_status varchar(20), ls_burger(# FOREIGN KEY (customer_id) ls_burger(# REFERENCES customers (id) ls_burger(# ON DELETE CASCADE ls_burger(# ); CREATE TABLE
Next we can create our
order_items table. This should have an
id to uniquely identify each order line, an
order_id field to reference the
orders table and a
product_id field to reference the
products table. We essentially use the
order_items table to create a Many-to-Many relationship between
ls_burger=# CREATE TABLE order_items ( ls_burger(# id serial PRIMARY KEY, ls_burger(# order_id integer, ls_burger(# product_id integer, ls_burger(# FOREIGN KEY (order_id) ls_burger(# REFERENCES orders (id) ls_burger(# ON DELETE CASCADE, ls_burger(# FOREIGN KEY (product_id) ls_burger(# REFERENCES products (id) ls_burger(# ON DELETE CASCADE ls_burger(# ); CREATE TABLE
Finally we can add our data. We add the data to
orders first. The
id values from the
orders table can then be used as the
order_id values in the
order_items table. Similarly, the
id values from the
products table are used as the
product_id values in the
ls_burger=# INSERT INTO orders (customer_id, order_status) ls_burger-# VALUES (1, 'In Progress'), ls_burger-# (2, 'Placed'), ls_burger-# (2, 'Complete'), ls_burger-# (3, 'Placed'); INSERT 0 4 ls_burger=# INSERT INTO order_items (order_id, product_id) ls_burger-# VALUES (1, 3), ls_burger-# (1, 5), ls_burger-# (1, 6), ls_burger-# (1, 8), ls_burger-# (2, 2), ls_burger-# (2, 5), ls_burger-# (2, 7), ls_burger-# (3, 4), ls_burger-# (3, 2), ls_burger-# (3, 5), ls_burger-# (3, 5), ls_burger-# (3, 10), ls_burger-# (3, 9), ls_burger-# (4, 1), ls_burger-# (4, 5); INSERT 0 15