DEV Community

Cover image for Milkco Database. An example database

Milkco Database. An example database

Jorge Castro
You are free to believe in whatever you want to, me too. So, stop preaching your religion, politics, or belief. Do you have facts? Then I will listen. Do you have a personal belief? Sorry but no.
Originally published at Updated on ・3 min read


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.

What is an example database?

It is an example database that could be used to experiment and test other functionalities

Alt Text

It is an example of visualization using Power BI

Alt Text


  • 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 table of products (skus) has a trends (some products are more sold than others)
  • The table of sales (invoices) 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


  • 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:
-- mysql 8.0
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- mysql <=5.7
Enter fullscreen mode Exit fullscreen mode
  • 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)

Alt Text

So, apparently, there is not a trend.

And Invoices per day week (January 2020)

Alt Text

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.
Enter fullscreen mode Exit fullscreen mode
  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.

Related link

Source code


Discussion (0)

Forem Open with the Forem app