How to remove a circular reference from relational database

Question

Let's say I'm building a chat which has groups and rooms. I need to create relational database scheme for it.

• Each group can have multiple rooms.
• And each group always has exactly 1 main room (which is also the same room as other)
• Room has already prebuilt (despite it's main room or not) structure and can't be duplicated. Like foreign keys to it, or description, notification settings etc.

So since it's one to many relationship the following db schema could represent this model:

,-----------------.
|Group            |
|-----------------|
|*id: pk          |
|*name: string    |
|*main_room_id: fk|
`-----------------'
      ↑      ?             
      ?      ↓       
  ,-------------.  
  |Room         |  
  |-------------|  
  |*id:         |  
  |*name: string|
  |*notif: bool |    
  |*group_id: fk|  
  `-------------'  

But I'd like to avoid circular reference in this scheme. I can do this by having boolean field in a room that says that this is a main room:

 ,-------------.  
 |Group        |  
 |-------------|  
 |*id: pk      |  
 |*name: string|  
 `-------------'  
         ↑        
         ?        
,----------------.
|Room            |
|----------------|
|*id:            |
|*name: string   |
|*notif: bool    |
|*group_id: fk   |
|*is_main: bool  |
`----------------'

But in using this scheme database is not normalized. Main room is_main boolean field depends on others room is_main field. Furthermore more complex constrain should be created to support data consistency.

What is the appropriate database schema for this issue?

question from:https://stackoverflow.com/questions/65876118/how-to-remove-a-circular-reference-from-relational-database

Answer 1

-- Group GRP exists.
--
group {GRP}
   PK {GRP}

-- Room ROM exists.
--
room {ROM}
  PK {ROM}

• Each room belongs to at most one group;
  each group may own more than one room.

-- Room ROM is owned by group GRP.
--
room_group {ROM, GRP}
        PK {ROM}
        SK {ROM, GRP}

FK1 {ROM} REFERENCES room  {ROM}
FK2 {GRP} REFERENCES group {GRP}

• Each group has at most one main room;
  each main room belongs to exactly one group.

• If a room is a main room for a group,
  then that room must belong to that group.

-- Room ROM is main room for group GRP.
--
main_room {GRP, ROM}
       PK {GRP}
       AK {ROM}

        FK {ROM, GRP} REFERENCES
room_group {ROM, GRP}

---

Notes:

Note that having a main room is not mandatory in the model, use application level to enforce it.

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key