One of the previous exercises was about managing smartphones. Today we want to consider how we can implement a modern SIM card management including call history.
In a company, SIM cards are issued that can be used freely by employees. Those SIM cards are uniquely identified globally via the ICCID. The special thing about the SIM cards is that they are Multi-SIMs. Each SIM has several IMSIs. This makes it possible to use different mobile network providers.
All calls are imported into a database at the end of the month for the necessary billing between the company and the mobile phone provider. The import consists of the IMSI of the mobile phone provider, the duration of the call, the destination phone number and the time the call had started.
As soon as an employee leaves the company, they have to return the SIM card. As soon as a new employee is hired, a free SIM card becomes the property of the new employee.
In the user interface of the SIM card management, only their own calls should appear for an employee. Calls from previous owners of the SIM card must not appear and must not be included in any calculations.
The management of employees is not relevant for this exercise: Model a single table containing an internal and public primary key to identify an employee?
Questions
- What is the format of an ICCID?
- What is the format of an IMSI?
- What could be a public primary key for an employee?
- How can be identified if a SIM card is currently in use?
Solution
Additional questions
- An ICCID is a unique 18-22 digit code that includes a SIM card's country, home network, and identification number.
- The IMSI number has up to 15 digits and consists of three parts: 3 digits MCC, two or three digits MNC, subsequent numbers are unique
- As a public primary key for an employee we can use his employee number. This can be different to the internal primary key. For an employee number you don't want to have "holes" in it: each employee number below the highest active employee number must have been assigned. Due to delete operations there could be missing employee numbers if you use an auto incremental primary key.
- For identifying SIM cards in use we can either add a status field for the SIM card itself (sim.status: ['assigned', 'free']) or we can just query if the SIM card as an active holder (
SELECT COUNT(*) FROM sim_history WHERE sim_id = ? AND end_at IS NULL will return 1 if is currently assigned).
Schema
Table employee {
id integer [primary key, not null, unique]
// our public primary key is the employee number. This can be different to our internal primary key. An employee number is mostly an incrementing number without any missing numbers in between.
number integer [not null, unique]
}
// Mobile phone provider
Table provider {
id integer [primary key, not null, unique]
name char(255) [not null]
}
Table sim {
id integer [primary key, not null, unique]
// ICCIDs are max 22 digits long
iccid varchar(22) [unique, not null]
}
// Who is the current holder of the SIM card?
Table sim_history {
id integer [primary key, not null, unique]
sim_id integer [not null, ref: > sim.id]
employee_id integer [not null, ref: > employee.id]
// This can't be null as a SIM card has either a holder or has no holder
begin_at datetime [not null]
// This can be null as the current employee is still the active holder
end_at datetime [null]
}
Table imsi {
id integer [primary key, not null, unique]
sim_id integer [not null, ref: > sim.id]
// Normally, a *SIM* would belong to a mobile phone provider. But with Multi-SIMs it is relevant that the IMSI belongs to it.
provider_id integer [not null, ref: > provider.id]
// The phone number of the IMSI might be relevant
phone_number varchar(32) [not null]
}
// Our import table for call history
Table call {
id integer [primary key, not null, unique]
imsi_id integer [not null, ref: > imsi.id]
// For a reference betweens calls and the holder of the IMSI at that time we can either use a JOIN for (call.begin_at >= sim_history.begin_at) or we identify during the import which SIM history entry is referenced for this IMSI.
// Using date fields for those JOINs complicates SQL queries. Because of this we'll go with the lookup during import:
sim_history_id integer [not null, ref: > sim_history.id]
// Target phone number
phone_number varchar(32) [not null]
// Start of call
begin_at datetime [not null]
// In seconds
duration integer [not null]
}