1

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.

Ivri
  • 2,159
  • 5
  • 25
  • 33
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Feb 27 '19 at 08:10

4 Answers4

1

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.
A.H.
  • 63,967
  • 15
  • 92
  • 126
0

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" ).

wildplasser
  • 43,142
  • 8
  • 66
  • 109
0

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.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
0

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.
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167