It is an example database for MYSQL, similar to Sakila, Adventureworks, other databases of example but it has a twist. Some information in this database, while it is random but it also as a bias or trend, so it is possible to use for data science and business intelligence.
Photo from unsplash.com/@jaypix_01
It is an example of visualization using Power BI
- Products (Skus) divided into types, subtypes, containers (cup, bottle, etc.) and brands.
- 3 Brands: Value Pack, Auntie Annie and Red Label (cheap, normal and expensive)
- More than 10 years of sales.
- A worldwide company with offices, employees and customers across the globe.
- The products has a trends (some products are more sold than others)
- The sales has seasonal trends in the number of invoices and in the size of the invoices.
- 50 Branches (offices) across the globe.
- 1000 Employees
- 17000 Invoices starting the 2005. The invoices have stationary trends.
- 64000 Invoice details. The invoices have stationary trends.
- 3563 Customers. Some of them are companies
- The database model is vanilla and clean. It does not have views, store procedure, functions or even index (with the exception of primary key and foreign keys)
- The columns are normalized to use the minimum type of definitions.
|Strings and texts
|Money and decimals
- Every Indexes have a prefix called "id" with the exception of "Sku", where "sku" is the index without a prefix.
- Foreign keys don't have a special name. Usually, they have the same name as the primary key.
- All tables are in plural. Composed names are written in camel case.
- Columns are written in lowercase and it starts in lowercase.
- It works with MySQL 8.0 or higher**. It could work with an older version of MySQL but you must replace the encoding:
![invoices_per_day](D:\dropbox\Projects\MilkCo-Database\invoices_per_day.jpg)-- mysql 8.0
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- mysql <=5.7
- It misses some features. For example, a table of purchases, the salaries and the costs of each branch are fixed
- Values are not inflationary.
- It lacks shipping
- It does not consider taxes. If the taxes are flat, then it is not a problem.
This database was created with random values. However, it has some trends
(I don't want to spoil much the results 😀)
For example, the invoices per day (January 2020)
So, apparently, there is not a trend.
And Invoices per day week (January 2020)
Did you see the trend?
How to install it?
- Create a new schema or use one.
CREATE SCHEMA `milkco` ; -- or you can use any name.
- Run the ddl.sql script
- It includes the structure of the database and the foreign key.s
- Run the dml.sql script.
- It includes the data of the database.