CODEWITHSUNDEEP
databasedatabase-schemarelational
Ivri
Ivri

Reputation: 2219

Relation "OR" in relational databases

The question is pretty trivial I guess. But nevertheless, E.g., I have Entities: user (id, name), group (id, name), user_group (user_id, group_id) and gallery (id, name, owner_id). Owner of the gallery could be user OR group.

What's the best solution for this in relational databases?

Thanks!

PS If anybody knows relational algebra and schema optimization. How will it look like?

I was thinking about Owner (id, user_id, group_id), but I don't have any idea how to show "OR" relation in terms of relational algebra.

Upvotes: 1

Views: 86

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Introduce a new entity OWNER and make it owner of the GROUP. Then make USER and GROUP categories of (i.e. "inherit" them from) OWNER.

Your ER model would look like this (only PK fields shown):

enter image description here

Theoretically, there are 3 major ways to implement a category in the physical database. All of them have pros and cons, but for your model, the solution 1 is probably the most appropriate:

  1. Use separate tables for OWNER, USER and GROUP and connect them via FOREIGN KEYs.
    • In this scenario, you may or may not use the discriminator (i.e. type identifier) in OWNER.
  2. Put USER and GROUP in the separate tables, with OWNER's fields present in both.
  3. Put OWNER, USER and GROUP in the same table.

Upvotes: 0

A.H.
A.H.

Reputation: 66263

The simplest solution would be a relation Owner(id, user_id, group_id) where either user_id or group_id can be set -- guard that with an appropriate constraint.

Collapsing Group and User into one table drags several consistency checks from the database into the application logic:

  • A group could have not only users but other groups (recursion and/or infinit cylces ahead).
  • If User and Group are used in some more places more adaptions might be necessary.
  • Data consistence cannot be enforced by the database any more.

Upvotes: 1

Tomalak
Tomalak

Reputation: 338248

There is no technical difference between a user and a group (only a conceptual one).

Put them both into the same table (user) and flag a row's type (group or user) in a second field.

Use application logic to make sure that only a row of type group may "have children" in the user_group table.

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

Combine owner and group into one table. Owner and group should only differ by the attributes they have in this table, or the presence / absence of rows in a relationtable joining this new table with itself ("is member of" ).

Upvotes: 0

Related Questions