For managing the inventory of his PC shop, the owner uses an Excel sheet. The following screenshot contains the current format:

The owner wants to migrate this Excel sheet to a relational database.
- How must a relational database must look like?
- What primary keys have to be used?
- What is referential integrity?
Solution
For the following solution we use a simple bank account implementation. You can also make it more specific as described in MODELLING_BANK_ACCOUNTS.
Schema

The diagram can also be found online on dbdiagram.io.
Table article {
id integer [not null, unique]
number integer [not null, unique]
name char(255) [not null]
amount integer [not null]
reorder_point integer [not null]
}
Table supplier {
id integer [not null, unique]
name char(255) [not null, unique]
street char(255) [not null]
city char(255) [not null]
// ZIP code in USA is 10 characters max
// @see https://www.oreilly.com/library/view/regular-expressions-cookbook/9781449327453/ch04s14.html
zip char(10) [not null]
}
Table account {
id integer [not null, unique]
iban char(34) [not null, unique]
supplier_id integer [not null, ref: > supplier.id]
bank_id integer [not null, ref: > bank.id]
}
// bank can be identified by the IBAN
Table bank {
id integer [not null, unique]
name char(255) [not null, unique]
// @see https://en.wikipedia.org/wiki/ISO_9362
bic char(11) [not null, unique]
}
Table article_in_supplier {
id integer [not null, unique]
article_id integer [not null, ref: > article.id]
supplier_id integer [not null, ref: > supplier.id]
price decimal(6,2) [not null]
}