Based upon a user request we want to design a database for firmware management of LED controller boards. The company of the user is specialized in the LED niche. In there, they are selling LED products. Most of the products are combined with a microcontroller which controls the LED sequences, colors and so on. Each of those products has a unique serial number. The serial number is persisted into the microcontroller and can not be changed.
Each microcontroller has a Bluetooth chip. A registered smartphone can now update the firmware of the LED controller with help of a native smartphone application.
The goal of the company is to manage new firmware versions of their products. Customers have to download and install the smartphone app first. With the initial run of the applicaiton, the customer has to register in the company's portal with his email address. After that, he is able to connect the smartphone to the LED via Bluetooth. During that process, the LED's serial number is sent to the company's portal. If the LED has not been already registered to that customer's account, it will be registered now.
In the app, the customer can now select any registered LED controller. For a registered LED controller, all firmware releases can be shown and installed.
The installation process itself is not relevant for the dabatase model.
Solution
Schema
// customer
Table customer {
id integer [primary key, not null, unique]
// a customer registers his account by his email adress
email char(255) [not null, unique]
}
// the company sells a bunch of products; we don't want to implement any inventory management for this exercise
Table product {
id integer [primary key, not null, unique]
name char(128) [not null]
}
// each product has a number of sellable instances
Table product_item {
id integer [primary key, not null, unique]
// this could be a GUID or a custom generated string; it must be unique for all each product
serial_key char(128) [not null]
// an item belongs to a product
product_id integer [not null, ref: > product.id]
}
// each product is registered during the registration process
Table product_item_registration {
id integer [primary key, not null, unique]
product_id integer [not null, ref: > product.id]
product_item_id integer [not null, ref: > product_item.id]
customer_id integer [not null, ref: > customer.id]
// a unique constraint [product_id, product_item_id] is required so that multiple customers can't own the same item
}
Table firmware {
id integer [primary key, not null, unique]
// a unique constraint could be added for [version, product_id]
// the same version could be used between different products
version char(8) [primary key, not null]
// we also allow null values so that the company can prepare new releases which are not yet shown to customers
released_at datetime [null]
// a firmware belongs to a product.
product_id integer [not null, ref: > product.id]
}