Milkco a fake database example

MilkCo-Database

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

Power Bi example using Mysql

Features

  • 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.
types Mysql Definition
Integer numbers int
Strings and texts varchar
Money and decimals decimal(10,2)
Booleans tinyint
Date DateTime
Timestamps Timestamp

Specifications

  • 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.

Considerations

  • 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
DEFAULT CHARSET=utf8 
  • 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.

Trends

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?

  1. Create a new schema or use one.
CREATE SCHEMA `milkco` ; -- or you can use any name.
  1. Run the ddl.sql script
    1. It includes the structure of the database and the foreign key.s
  2. Run the dml.sql script.
    1. It includes the data of the database.

Source Code

https://github.com/EFTEC/MilkCo-Database

About Jorge Castro

Currently: Entrepreneur and Private Consultant
Civil Engineer in Informatics - USACH Chile.
Master in Business Administration (MBA) CEPADE Spain
Microsoft Certified Professional
Oracle Certified Associate
ScrumMaster Certified
Former developer
Former Project Manager

Related Posts