Design a Motorbike producer Database — Example ER diagram + SQL code

This database is designed to briefly represent the connections in a database system for a motorbike producer. We assume here that a company has various business intelligence dashboards to track and analyze its operations and performance across different areas of its business. We will consider the following information: sales, production, dealers, and employees. We will not take a look at the marketing dashboards in this case.

Let’s look at the example. As you can see on the ER diagram, there is a database with four interconnected tables showing the motorbike producer operations. The diagram is created with the free tool DrawSql.

ER Diagram Motorbike Store

Below we will consider what information could be represented in the tables. You can find the code for this database system on GitHub.

1. Table ‘Sales’

The most important table stores information about vehicles, numbers of units sold, and essential dates such as order date, sales date, delivery date, costs, etc.

Table Sales — Motorbike Database system

As you can see, we have a vehicle name, for example, Cafe Racer Mororradumbau, with a price of 8900 euros that belongs to the C series. One item was sold in the United States and delivered on the third of February, 2023. Production costs equal to 4000 euro, the motorbike was sold through leasing by a dealer with the help of our employee.

The table contains foreign keys — vehicle name, dealer id, and employee id with reference to the other tables. It is essential to notice that foreign key constraints must be implemented to prevent data inconsistencies and errors. If we do not specify foreign keys, our values will not be updated in the related tables during the actualization.

2. Table ‘Inventory’

The table represents the list of vehicles and their characteristics, such as price, weight, runtime, and the number of available units.

Table Inventory — Motorbike Database system

3. Table ‘Dealers’

This table includes information about dealers who sell our products. For instance, our dealer Momentum with dealer zip 43110 whose id 502 is responsible for sales in the American region.

4. Table ‘Employees’

Here we can find the information about our employees, their salaries and sold items.

So, you can perform a wide range of operations with this data, such as:

1. Total sales and volume
2. The average price for a vehicle
3. Number of motorbikes that were sold using leasing and/or loan
4. Profit
5. Sales growth
6. Minimum and maximum sales
7. Dealers located in Italy
8. An employee brought the most sales

I will provide possible queries in the following article.

Thank you for reading.