Design

Tables

Relation view of tables from phpmyadmin

Store (id, location_name, address, postcode, city)

Inventory (id, store_id FK, product_id FK, quantity)

Product (id, name, description)

SaleProduct (id, product_id, sale_id, quantity)

Sale (id, store_id FK, customer_id FK, employee_id FK)

Employee (id, first_name, last_name, store_id FK, salary, role)

Customer (id, first_name, last_name, dob, email, phone_number)

Entity Relation Diagram

ERD of the database's tables

Data dictionary

`Store`

-- A physical store location
CREATE TABLE Store (
	id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    location_name varchar(32)NOT NULL,
    address varchar(56) NOT NULL,
    postcode int(4) NOT NULL,
    city varchar(32) NOT NULL
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`location_name`	String	32	N/A	Name of the store	Required
`address`	String	56	N/A	Street address of the store	Required
`postcode`	Integer	4	N/A	Postcode of the store	Required
`city`	String	40	N/A	City store is located in	Required

`Employee`

-- A person who works at a store
CREATE TABLE Employee (
    id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    first_name varchar(24) NOT NULL,
    last_name varchar(32),
    salary int(11),
    role varchar(24),
    store_id int(11) NOT NULL,
    FOREIGN KEY (store_id) REFERENCES Store(id)
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`first_name`	String	24	N/A	Employee first name	Required
`last_name`	String	32	N/A	Employee last name	Optional
`salary`	Integer	11	N/A	Yearly salary	Optional
`role`	String	24	N/A	Position of employee	Optional. Limited values
`store_id`	Integer	11	N/A	Foreign key to `Store`	Required

`Customer`

-- A person with a store membership
CREATE TABLE Customer (
	id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    first_name varchar(24) NOT NULL,
    last_name varchar(32),
    dob date,
    email varchar(56),
    phone_number varchar(16)
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`first_name`	String	24	N/A	Customer first name	Required
`last_name`	String	32	N/A	Customer last name	Optional
`dob`	Date	N/A	N/A	Customer date of birth	Optional
`email`	String	56	N/A	Customer email address	Optional
`phone_number`	String	16	N/A	Customer phone number	Optional

`Product`

-- An item sold by the shop
CREATE TABLE Product (
    id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name varchar(56) NOT NULL,
    description varchar(128)
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`name`	String	56	N/A	Descriptive name for product	Required
`description`	String	128	null	Description of product	Optional

`Inventory`

-- The quantity of a given product a store has
CREATE TABLE Inventory (
	id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    store_id int(11) NOT NULL,
    FOREIGN KEY (store_id) REFERENCES Store(id),
    product_id int(11) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES Product(id),
    quantity int(11) NOT NULL
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`store_id`	Integer	11	N/A	Foreign key to `Store`	Required
`product_id`	Integer	11	N/A	Foreign key to `Product`	Required
`quantity`	Integer	11	N/A	Amount of stock in store	Required

`Sale`

-- An in-store purchase by a customer
CREATE TABLE Sale (
	id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    store_id int(11) NOT NULL,
    FOREIGN KEY (store_id) REFERENCES Store(id),
    customer_id int(11) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customer(id),
    employee_id int(11) NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(id)
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`store_id`	Integer	11	N/A	Foreign key to `Store`	Required
`customer_id`	Integer	11	N/A	Foreign key to `Customer`	Required
`employee_id`	Integer	11	N/A	Foreign key to `Employee`	Required

`SaleProduct`

-- Joining table for Sale and Product
CREATE TABLE SaleProduct (
	id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    product_id int(11) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES Product(id),
    sale_id int(11) NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES Sale(id),
    quantity int(11) NOT NULL
);

Element name	Type	Size	Default	Description	Constraint
`id`	Integer	11	A_I	Primary key for table	Required. Auto increment
`product_id`	Integer	11	N/A	Foreign key to `Product`	Required
`sale_id`	Integer	11	N/A	Foreign key to `Sale`	Required
`quantity`	Integer	11	N/A	Product count in sale	Required

You can interact with a live copy of this database using the playground. No need to install or import database files locally!

Analysis

Development