Scribd
Upload
70 views14 pages

MySQL Demo Physical Data Model

This document provides details on the physical data model for a MySQL database, including tables, views, and references. It describes 14 tables that contain information on products, orders, shipments, payments, clients, locations, and more. It also defines 1 view and 12 references between the tables. The goal of the model is to store transactional data for an e-commerce site in MySQL.

Uploaded by

John Aldana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
70 views14 pages

MySQL Demo Physical Data Model

This document provides details on the physical data model for a MySQL database, including tables, views, and references. It describes 14 tables that contain information on products, orders, shipments, payments, clients, locations, and more. It also defines 1 view and 12 references between the tables. The goal of the model is to store transactional data for an e-commerce site in MySQL.

Uploaded by

John Aldana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 14

Database model: MySQL demo physical data model, mysql

Database model documentation

1
Database model: MySQL demo physical data model, mysql

Table of contents

1. Model details 3
2. Tables 4
1.1. Table product 4
1.2. Table product_type 4
1.3. Table stock 4
1.4. Table shipment 4
1.5. Table client 5
1.6. Table city 5
1.7. Table country 5
1.8. Table shipment_details 6
1.9. Table shipment_type 6
1.10. Table payment_type 6
1.11. Table payment_data 6
1.12. Table payment_details 7
1.13. Table status_catalog 7
1.14. Table shipment_status 7
3. Views 9
2.1. View product_details 9
4. References 10
3.1. Reference stock_product 10
3.2. Reference product_product_type 10
3.3. Reference city_country 10
3.4. Reference client_city 10
3.5. Reference shipment_client 10
3.6. Reference shipmet_details_shipment 10
3.7. Reference shipmet_details_product 10
3.8. Reference shipment_shipment_type 10
3.9. Reference shipment_payment_type 11
3.10. Reference payment_data_payment_type 11
3.11. Reference payment_details_shipment 11
3.12. Reference payment_details_payment_data 11
3.13. Reference shipment_status_shipment 11
3.14. Reference shipment_status_status_catalog 11
5. Subject areas 12

2
Database model: MySQL demo physical data model, mysql

1. Model details
Model name:
MySQL demo physical data model
Version:
2.4
Database engine:
MySQL
Description:

3
Database model: MySQL demo physical data model, mysql

2. Tables
2.1. Table product
2.1.1. Columns

Column name Type Properties Description


id int PK
product_name varchar(64)
product_descript varchar(255)
ion
product_type_id int
unit varchar(16)
price_per_unit decimal(8,2)

2.2. Table product_type


2.2.1. Columns

Column name Type Properties Description


id int PK
type_name varchar(64)

2.3. Table stock


2.3.1. Columns

Column name Type Properties Description


product_id int PK
in_stock decimal(8,2)
last_update_time timestamp

2.4. Table shipment


2.4.1. Columns

Column name Type Properties Description


id int PK
client_id int
time_created timestamp

4
Database model: MySQL demo physical data model, mysql

shipment_type_id int
payment_type_id int
shipping_address text
billing_address text
products_price decimal(8,2)
delivery_cost decimal(8,2)
discount decimal(8,2)
final_price decimal(8,2)

2.5. Table client


2.5.1. Columns

Column name Type Properties Description


id int PK
first_name varchar(128)
last_name varchar(128)
company_name varchar(128) null
VAT_ID varchar(64) null
city_id int
client_address text

2.6. Table city


2.6.1. Columns

Column name Type Properties Description


id int PK
city_name varchar(128)
country_id int

2.7. Table country


2.7.1. Columns

Column name Type Properties Description


id int PK
country_name varchar(128)

5
Database model: MySQL demo physical data model, mysql

2.8. Table shipment_details


2.8.1. Columns

Column name Type Properties Description


id int PK
shipment_id int
product_id int
quanitity decimal(8,2)
price_per_unit decimal(8,2)
price decimal(8,2)

2.8.2. Alternate keys

Key name Columns Description


shipment_id,
shipmet_details_ak_1 product_id

2.9. Table shipment_type


Description:
e.g. send after payment, charge after delivery, ...
2.9.1. Columns

Column name Type Properties Description


id int PK
type_name varchar(64)

2.10. Table payment_type


Description:
e.g. card, cash, paypal, wire transfer
2.10.1. Columns

Column name Type Properties Description


id int PK
type_name varchar(64)

2.11. Table payment_data

6
Database model: MySQL demo physical data model, mysql

2.11.1. Columns

Column name Type Properties Description


id int PK
payment_type_id int
data_name varchar(255)
data_type varchar(255)

2.11.2. Alternate keys

Key name Columns Description


payment_type_id,
payment_data_ak_1 data_name

2.12. Table payment_details


2.12.1. Columns

Column name Type Properties Description


id int PK
shipment_id int
payment_data_id int
value varchar(255)

2.13. Table status_catalog


Description:
list of all possible statuses: ordered, paid, delivered
2.13.1. Columns

Column name Type Properties Description


id int PK
status_name varchar(255)

2.14. Table shipment_status


2.14.1. Columns

Column name Type Properties Description


id int PK

7
Database model: MySQL demo physical data model, mysql

shipment_id int
status_catalog_i int
d
status_time timestamp
notes text null

8
Database model: MySQL demo physical data model, mysql

3. Views
3.1. View product_details
SQL:
SELECT
p.id,
p.product_name,
p.product_description,
pt.type_name,
p.unit,
p.price_per_unit,
s.in_stock,
s.last_update_time
FROM product p
LEFT JOIN product_type pt ON p.product_type_id = pt.id
LEFT JOIN stock s ON p.id = s.product_id;
3.1.1. Columns

Column name Type Properties Description


id int
product_name varchar(64)
product_descript varchar(255)
ion
type_name varchar(64)
unit varchar(16)
price_per_unit decimal(8,2)
in_stock decimal(8,2)
last_update_time timestamp

9
Database model: MySQL demo physical data model, mysql

4. References
4.1. Reference stock_product
product 0..* stock
id <-> product_id

4.2. Reference product_product_type


product_type 0..* product
id <-> product_type_id

4.3. Reference city_country


country 0..* city
id <-> country_id

4.4. Reference client_city


city 0..* client
id <-> city_id

4.5. Reference shipment_client


client 0..* shipment
id <-> client_id

4.6. Reference shipmet_details_shipment


shipment 0..* shipment_details
id <-> shipment_id

4.7. Reference shipmet_details_product


product 0..* shipment_details
id <-> product_id

4.8. Reference shipment_shipment_type

10
Database model: MySQL demo physical data model, mysql

shipment_type 0..* shipment


id <-> shipment_type_id

4.9. Reference shipment_payment_type


payment_type 0..* shipment
id <-> payment_type_id

4.10. Reference payment_data_payment_type


payment_type 0..* payment_data
id <-> payment_type_id

4.11. Reference payment_details_shipment


shipment 0..* payment_details
id <-> shipment_id

4.12. Reference payment_details_payment_data


payment_data 0..* payment_details
id <-> payment_data_id

4.13. Reference shipment_status_shipment


shipment 0..* shipment_status
id <-> shipment_id

4.14. Reference shipment_status_status_catalog


status_catalog 0..* shipment_status
id <-> status_catalog_id

11
Database model: MySQL demo physical data model, mysql

5. Areas
5.1. Product subject area
5.1.1. Tables
- product
- product_type
- stock
5.1.2. Views
- product_details
5.1.3. References
- stock_product
- product_product_type
- city_country
- client_city
- shipment_client
- shipmet_details_shipment
- shipmet_details_product
- shipment_shipment_type
- shipment_payment_type
- payment_data_payment_type
- payment_details_shipment
- payment_details_payment_data
- shipment_status_shipment
- shipment_status_status_catalog

5.2. Shipment subject area


5.2.1. Tables
- shipment
- shipment_details
- shipment_type
- status_catalog
- shipment_status
5.2.2. References
- stock_product
- product_product_type
- city_country
- client_city
- shipment_client

12
Database model: MySQL demo physical data model, mysql

- shipmet_details_shipment
- shipmet_details_product
- shipment_shipment_type
- shipment_payment_type
- payment_data_payment_type
- payment_details_shipment
- payment_details_payment_data
- shipment_status_shipment
- shipment_status_status_catalog

5.3. Client subject area


5.3.1. Tables
- client
- city
- country
5.3.2. References
- stock_product
- product_product_type
- city_country
- client_city
- shipment_client
- shipmet_details_shipment
- shipmet_details_product
- shipment_shipment_type
- shipment_payment_type
- payment_data_payment_type
- payment_details_shipment
- payment_details_payment_data
- shipment_status_shipment
- shipment_status_status_catalog

5.4. Payment subject area


5.4.1. Tables
- payment_data
- payment_details
5.4.2. References
- stock_product
- product_product_type
- city_country

13
Database model: MySQL demo physical data model, mysql

- client_city
- shipment_client
- shipmet_details_shipment
- shipmet_details_product
- shipment_shipment_type
- shipment_payment_type
- payment_data_payment_type
- payment_details_shipment
- payment_details_payment_data
- shipment_status_shipment
- shipment_status_status_catalog

14

You might also like