1

I have a hierarchical data model I am creating in a SQL / relational database.

The relevant entities are:

  • Institutions have one or more
  • Buildings, which have one or more
  • Rooms

(Each Room has exactly one parent Building, and each Building has exactly one parent Institution).

There are also Users who can have some roles at any level on the hierarchy. The possible Roles are:

  • Owner
  • Property manager
  • Occupant

Having a role at one level of the hierarchy implicitly grant that role at all lower levels (e.g. if someone is an Owner of an Institution, they are considered to be an Owner of all Buildings and Rooms in that institution).

Some examples:

  • Alice is an:
    • Owner of Institution A1 (and therefore implicitly an owner of all buildlings and rooms belonging to that institution)
    • Occupant of Building B1, B2, and B7 in Institution B
  • Bob is an Occupant of Room R1 in Institution X's Building Y
  • Cindy is an:
    • Property manager of Building-B1 of institution B
    • Property manager of Building C3 of Institution C
    • Owner of Institution D
    • Occupant of Room R7, R8, and R11 in Institution M's Building N

What is a good way of modeling these user roles across these entities in a relational database?

deadcode
  • 2,226
  • 1
  • 20
  • 29
  • Well, what is *some* way? What have you tried? – philipxy Mar 30 '17 at 11:19
  • @philipxy Ideas: Idea1: Make a many to many role table between user and each level of the hierarchy. E.g. user_institution_roles, user_building_roles, etc.. Then to answer role questions you have to consult 3 tables. Idea2: Make a table with an FK to user, and optional FKs to each level of the hierarchy. Then to query what roles a user has on room R you need to first look up the building and institution of R and have a where clause like: user_id = 123 and ( institution_id = R_institution OR building_id = R_building OR room = R ). Neither of these seems appealing. – deadcode Mar 30 '17 at 18:10
  • 1. Please edit that into your question, comments are not for clarifications and are ephemeral. 2. Explain *why* not "appealing", and in technical terms. See also faq http://stackoverflow.com/q/4048151/3404097. – philipxy Mar 30 '17 at 23:07
  • The first thing you need to understand is that a group has the same super type as an individual. For example, a User is a Role in Postgres. Also, you want to support inheritance. Can you not use your db's existing role system? – Neil McGuigan Apr 12 '17 at 18:40
  • @deadcode can you describe the solution you have implemented? – CuriousMind Oct 15 '19 at 11:52

0 Answers0