Design a Bike shop Database — Example ER diagram + SQL code
Suppose your business is still using multiple Excel tables. In that case, it’s time to create a database system with a logical structure that will help you to automate, secure, and optimize the process. In this article, we will tackle the problem of creating a database for a bike store that sells and rents our bikes and e-bikes.
So, this store has a subscription model, the rental stations and operates day-to-day revenue by presenting its products on Google and other stores.
Let’s look at the example. As you can see on the ER diagram, there is a database with five interconnected tables showing bike store operations. The diagram is created with the free tool DrawSql.
Below we will consider what information could be represented in the tables.
1. Table ‘Customers’
The key information will be regarding the bikes, their names, and different dimensions such as size, weight, color, etc. That’s why we need to create a table with our products.
2. Table ‘Platforms’
The second important table is Platforms, those websites where you place your offering, for example, Amazon, Decathlon, and Google Shopping.
3. Table ‘Orders’
As a marketplace, we probably have a long list of orders with information about the order status. Still, also various dates such us required date and date of shipment. And of course, we should have a separate table for our customers.
4. Table ‘Work With’
The table Works With represents additional information connecting orders and bikes.
It is important to understand that every bike is unique because even if two bikes have the same model, they definitely have different bike mileage.
So, you can perform a wide range of operations with this data, such as:
- Find a particular bike
- Calculate the income for each bike
- Calculate dates when a bike was delivered
You can find the code on GitHub.
Thank you for reading.
