ROYLE

carts

Stores information about products added to users' shopping carts

Name	Type	Nullable	Constraints	Referenced	Default	Extra	Comment
added_date	timestamp	YES			CURRENT_TIMESTAMP	DEFAULT_GENERATED	Date and time when the product was added to the cart
cart_id	int	NO	PRIMARY KEY			auto_increment	Unique identifier for each shopping cart
product_id	int	NO	FOREIGN KEY	products			Identifier of the product in the cart, references products(product_id)
quantity	int	NO			1		Number of units of the product added to the cart
user_id	int	NO	FOREIGN KEY	users			Identifier of the user who owns the cart, references users(user_id)

Name	Type	Nullable	Constraints	Extra	Comment
category_id	int	NO	PRIMARY KEY	auto_increment	Unique identifier for each product category
category_name	varchar(50)	NO			Name of the category used to group similar products
description	text	YES			Detailed description of the product category, including examples and usage

Stores detailed information about employees, including personal details and job-related information

Name	Type	Nullable	Constraints	Referenced	Extra	Comment
address	varchar(100)	YES				Residential address of the employee
birth_date	date	NO				Birth date of the employee
city	varchar(50)	YES				City where the employee resides
country	varchar(50)	NO				Country where the employee resides
employee_id	int	NO	PRIMARY KEY		auto_increment	Unique identifier for each employee
first_name	varchar(50)	NO				First name of the employee
hire_date	date	NO				Date when the employee was hired
last_name	varchar(50)	NO				Last name of the employee
manager_id	int	YES	FOREIGN KEY	employees		Identifier of the employee's manager, references employees(employee_id)
notes	text	YES				Additional notes or comments about the employee
phone	varchar(20)	YES				Contact phone number of the employee
photo	blob	YES				Photograph of the employee
postal_code	varchar(20)	YES				Postal code of the employee's residential address
region_id	int	YES	FOREIGN KEY	regions		Identifier of the region where the employee works, references regions(region_id)

Contains invoice information for orders, including total amount and invoice date

Name	Type	Nullable	Constraints	Referenced	Default	Extra	Comment
amount	decimal(10,2)	NO					Total amount to be paid as per the invoice
invoice_date	timestamp	YES			CURRENT_TIMESTAMP	DEFAULT_GENERATED	Date and time when the invoice was generated
invoice_id	int	NO	PRIMARY KEY			auto_increment	Unique identifier for each invoice issued
order_id	int	NO	FOREIGN KEY	orders			Identifier of the order associated with this invoice, references orders(order_id)

Stores details for each product included in a customer order

Name	Type	Nullable	Constraints	Referenced	Default	Extra	Comment
detail_id	int	NO	PRIMARY KEY			auto_increment	Unique identifier for each order line item
order_id	int	NO	FOREIGN KEY	orders			Identifier of the order this line item belongs to, references orders(order_id)
product_id	int	NO	FOREIGN KEY	products			Identifier of the product in this line item, references products(product_id)
quantity	int	NO			1		Number of units of the product ordered
subtotal	decimal(10,2)	NO					Subtotal amount for this line item (quantity * product price)

Contains customer order information including order date and total amount

Name	Type	Nullable	Constraints	Referenced	Default	Extra	Comment
order_date	timestamp	YES			CURRENT_TIMESTAMP	DEFAULT_GENERATED	Date and time when the order was created
order_id	int	NO	PRIMARY KEY			auto_increment	Unique identifier for each customer order
total_amount	decimal(10,2)	NO					Total monetary value of the order, including taxes and shipping
user_id	int	NO	FOREIGN KEY	users			Identifier of the user who placed the order, references users(user_id)

Stores information about products available for sale, including pricing and stock levels

Name	Type	Nullable	Constraints	Default	Extra	Comment
description	text	YES				Detailed description of the product, including features and specifications
price	decimal(10,2)	NO				Retail price of the product in the store's currency
product_id	int	NO	PRIMARY KEY		auto_increment	Unique identifier for each product in the catalog
product_name	varchar(100)	NO				Name of the product as displayed to customers
stock_quantity	int	NO		0		Current stock level of the product, used for inventory management

Stores information about different geographical regions, used for shipping and employee assignments

Name	Type	Nullable	Constraints	Extra	Comment
country	varchar(50)	NO			Country where the region is located
region_id	int	NO	PRIMARY KEY	auto_increment	Unique identifier for each geographical region
region_name	varchar(50)	NO			Name of the geographical region

Stores user-generated reviews for products, including ratings and comments

Name	Type	Nullable	Constraints	Referenced	Default	Extra	Comment
comment	text	YES					Textual feedback provided by the user about the product
product_id	int	NO	FOREIGN KEY	products			Identifier of the product being reviewed, references products(product_id)
rating	int	NO					Numeric rating given by the user to the product, usually from 1 to 5
review_date	timestamp	YES			CURRENT_TIMESTAMP	DEFAULT_GENERATED	Date and time when the review was submitted
review_id	int	NO	PRIMARY KEY			auto_increment	Unique identifier for each product review
user_id	int	NO	FOREIGN KEY	users			Identifier of the user who wrote the review, references users(user_id)

Contains information about shipping companies responsible for delivering orders

Name	Type	Nullable	Constraints	Extra	Comment
phone	varchar(20)	NO			Contact phone number for the shipping company
shipper_id	int	NO	PRIMARY KEY	auto_increment	Unique identifier for each shipping company
shipper_name	varchar(100)	NO			Name of the shipping company

Stores basic information about users including login credentials and administrative status

Name	Type	Nullable	Constraints	Default	Extra	Comment
email	varchar(100)	NO	UNIQUE			User's email address, used for notifications and password recovery
is_admin	tinyint(1)	YES		0		Indicates whether the user has administrative privileges (TRUE/FALSE)
last_login	timestamp	YES				Date and time when the user last logged into the system
password_hash	varchar(255)	NO				Securely stored hashed password for user authentication
registration_date	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED	Date and time when the user registered in the system
user_id	int	NO	PRIMARY KEY		auto_increment	Unique identifier for each user in the system
username	varchar(50)	NO				User-selected name for logging into the system, must be unique