SQL schema doubts

Question

I wonder if this UML is valid, the "user_building_divisions" seems weird to me.

A user can create a record of food wasted in a building and each building can have multiple divisions, so a user works in a building and is assigned to a division, for example technology.

But some users can work in different buildings. So if I don't have that table when I create the record, I don't have the information on which building and division I can add the record to because it will only be to one of the buildings and divisions that the user has access to.

score 1 · Answer 1 · answered Mar 23 '21 at 18:29

-- Building BLD exists.
--
building {BLD}
      PK {BLD}

-- Division DIV (of a building) exists.
--
division {DIV}
      PK {DIV}

-- Building BLD has division DIV.
--
building_division {BLD, DIV}
               PK {BLD, DIV}

FK1 {BLD} REFERENCES building {BLD}
FK2 {DIV} REFERENCES division {DIV}

-- User USR exists.
--
user {USR}
  PK {USR}

-- User USR is assigned to building BLD,
-- division DIV.
--
user_building {USR, BLD, DIV}
           PK {USR, BLD, DIV}

FK1 {USR} REFERENCES user {USR}

              FK2 {BLD, DIV} REFERENCES 
building_division {BLD, DIV}

-- At (date-time) DTE, user USR created
-- a food-wasting record 
-- for building BLD, division DIV.
--
record_waste {USR, DTE, BLD, DIV}
          PK {USR, DTE}

          FK1 {USR, BLD, DIV} REFERENCES
user_building {USR, BLD, DIV}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

If you need, or prefer, single-column PKs ADD them as described in this example.

Nice to see we are suggesting the same data model. You build this on composite keys, while mine doesn't. For this small model this doesn't make much of a difference, but in general I consider a model based on composite keys superior. +1 from me. — Thorsten Kettner, Mar 23 '21 at 22:32

Thorsten Kettner · Answer 2 · 2021-03-22T21:02:45.817

You are mixing two things here. There is an m:n relation between user and building_divisions. In an RDBMS you would model this with a bridge table (your user_building_divisions table). So either exclude this table from your UML and connect user and building_divisions with an m:n line or keep this table in your UML and link it with 1:n lines two its two parent tables.

There are more mistakes with m:n in your UML. A division has multiple building_division. But a building division belongs to only one division. That is 1:n. Same for building and building_division.

As to your overall data model:

Do you find it necessary to split the user data into two 1:1 related tables? I find it just makes the model unnecessarily complicated.
I think that you could model the tables in one line more or less: building and division make building divisions. A user links to building divisions. A user creates records on their linked building divisions.

This is how I'd model this:

user: user_id, email, country ...
  (unique constraint on email I guess)

building: building_id, name
  (unique constraint on name)

division: division_id, name
  (unique constraint on name)

building_division: building_division_id, building_id, division_id
  (unique constraint on building_id + division_id)

user_building_division: user_building_division_id, user_no, building_division_id
  (unique constraint on user_no + building_division_id)

user_building_division_record: user_building_division_record_id, user_building_division_id, ...

I understand everything except this "So either exclude this table from your UML and connect user and building_divisions with an m:n line " If I do this m:n line, I need always an extra table "user_building_divisions" to handle it right? — Gerardlc, Mar 23 '21 at 19:22
Okay, I admit that was not clear. In a database you need a bridge table (also called junction table sometimes) to establish an m:n relation. In UML it suffices to draw a line and label the ends with m and n. But you can refine your UML to the point where you dissolve all m:n lines and show the bridge tables instead. Your bridge table `user_building_divisions` replaces/refines the m:n line you would otherwise have between `user` and `building_divisions`. It is 1:n related to the two tables, because one `user` can have many `user_building_divisions`, but one `user_building_divisions` belongs ... — Thorsten Kettner, Mar 23 '21 at 22:14
... to one `user`. Same for `user_building_divisions` and `building_divisions`: one `building_divisions` row can have many `user_building_divisions` rows, but one `user_building_divisions` row belongs to exactly one `building_divisions` row. So by showing the bridge tables in the UML you get rid of all m:n lines. Just decide how deep you model your UML. Either you show the real entities linked by 1:n and m:n or include the bridge tables thus losing all m:n lines. Or draw both, so as to have two models, one showing the business relations, one showing the database. — Thorsten Kettner, Mar 23 '21 at 22:21

SQL schema doubts

2 Answers2