Shopping list assistant¶

Problem definition¶

You have developed an application called shopApp, an assistant that helps users create shopping lists. You need to develop a database for your application so that you can store the information about the user (at least a unique user name, the full name, and an email address), and the items (at least the product description). Each user can manage different shopping list. For each shopping list, you need to store at least the name, and a reference to the user. Finally, you need to store the list of items in each shopping list, bearing in mind that a product can appear in several shopping lists. Design a data model to implement a database for shopApp. You may add any additional field you consider relevant

Solution¶

Data model¶

The image below shows the data model. Shop App Data Model

This will create a database called “shopApp” and three tables: “users”, “shopping_lists”, and “shopping_list_items”. The “users” table will store the information about the user, the “shopping_lists” table will store the information about the shopping lists, and the “shopping_list_items” table will store the information about the items in each shopping list. The tables are linked with foreign keys to ensure referential integrity.

Implementation¶

The following SQL script creates the database in the model below.

CREATE DATABASE shopApp;

USE shopApp;

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE shopping_lists (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE shopping_list_items (
  id INT NOT NULL AUTO_INCREMENT,
  shopping_list_id INT NOT NULL,
  product_description VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (shopping_list_id) REFERENCES shopping_lists(id)
);