lampant is an SaaS web application for collecting new ideas from employees how to improve the business.
After a user has registered a new tenant in lampant, this user is assigned as an administrator for the newly created tenant. The administrator can configure a DNS domain, so that newly signed up users are automatically assigned to this tenant, if their e-mail address domain matches the tenant's configured domain.
Having finished the initial configuration, the administrator can add topics, e.g. Employee Engagement, Office, Sustainability.
Each user assigned to the tenant can then add new ideas for each topic. An idea consists upon a subject and a body. For the topic Office there could be an idea Add plants to all rooms.
Other users can vote for an idea. A user can not vote for the same idea more than one time. Also, each user can write any number comments for an idea to discuss it.
Dashboard overview
Hints
- For this exercise, you can skip the common username, password, verified_at fields for user management.
- Modelling the Award mechanism in the screenshot above is not part of this exercise.
- A user, identified by its email, can only be attached to one tenant.
- We assume that multi-tenancy is modelled by having a shared schema between all tenants. Try to model the database by using only two foreign keys pointing to the tenant entity. The two tables containing the foreign keys are serving as root entity for other entities.
Solution
Schema
// Let's assume that one user has exactly one Podcast show
Table tenant {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
domain char(255) [null]
is_domain_verified boolean [null]
}
// a user can be seen as root aggregate, so we attach the tenant_id to it
Table user {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
tenant_id integer [not null, ref: > tenant.id]
email char(255) [not null, unique]
role role [not null, default: 'user']
}
enum role {
admin
user
}
// a topic can be seen as root aggregate, so we attach the tenant_id to it
Table topic {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
tenant_id integer [not null, ref: > tenant.id]
name char(255) [not null]
icon char(255) [null]
}
Table idea {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
topic_id integer [not null, ref: > topic.id]
// creator
user_id integer [not null, ref: > user.id]
subject char(255) [not null]
body text [null]
created_at datetime [not null]
}
Table vote {
id integer [primary key, not null, unique]
// no public GUID required as the vote probably can not be edited
user_id integer [not null, ref: > user.id]
idea_id integer [not null, ref: > idea.id]
// a constraint can be added so that (user_id, idea_id) is unique, this prevents multiple votes for an idea by the same user
created_at datetime [not null]
}
// for comments we assume that they are plain an no threading is available
Table comment {
id integer [primary key, not null, unique]
// comments may be deleted
guid uuid [not null, unique]
user_id integer [not null, ref: > user.id]
idea_id integer [not null, ref: > idea.id]
body text [not null]
created_at datetime [not null]
}