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 null (job AAA|BBB works for all users that have either AAA|BBB or 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_1, level_2, etc. but I think it should work. But I also think there are better ways to implement this.

  • 1
    Can you show what you have so far? We're not going to design it from scratch for you. – LowlyDBA Feb 4 '15 at 14:06
  • @JohnM added my attempt in the OP. – confused00 Feb 4 '15 at 14:29

I'd probably go for 3 tables,

  • department
  • department alert
  • employee

and give the following relationships

  • Employee to department
  • department alert onto department
  • department onto department (self join)

Assuming your RDBMS can handle it, I'd then use a recursive CTE query to find every related department and join to alerts to get all of the alerts required.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.