I have a data model where each user profile belongs to a department. Each job is also available for a specific department. The department names are formatted like this: AAA-BBB-CCC-DDD-EEE, but of variable lenght - e.g. one user may have only 3 levels ('AAA-BBB-CCC', and other user may have 2 levels - 'XYZ-WVM'), with a maximum of 5 levels depth. The other requirement is that when a job is posted for a specific department, it should be visible to all users from that department, from above levels, and lower levels that share the same hierachy (so both ancestors and descendants) as. Example:
Job Z is posted for department 'AAA-BBB-CCC-DDD'. This means it should be available to all users from that same department, but also to users whose department is just 'AAA-BBB-CCC' or 'AAA-BBB' or 'AAA'. Not for users whose department is 'AAA-XXX' though. The job should also be available to users from 'AAA-BBB-CCC-DDD-EEE', 'AAA-BBB-CCC-DDD-XXX'.
Another requirement is that new departments should be easy to add.
What's a good design for this job? I'm using a relational database (SQLite for the moment).
Common operations would include:
- Check what jobs should be displayed for a user
- Send emails to users for whom the job is relevant
EDIT (to add my own attempt):
Right now, I'm thinking about having a
Departments table where each row has a column dedicated for each level where values are either the respective level or
null - so AAA-BBB-CCC has
AAA|BBB|CCC|null|null. When I want to see if a job is available for a specific user, I check if the job's department "contradicts" with the user department where a contradiction means that 2 equivalent columns have a different value that is not
AAA|BBB works for all users that have either
AAA|null - so
AAA|BBB|XXX is accepted too).
This adds a lot of
null values in the table, and I feel weird having 5 columns
level_2, etc. but I think it should work. But I also think there are better ways to implement this.