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