0

I am trying to design the schema for a live-action game application that supports multiple games. I have several tables that share multiple attributes such as: Assassins_Participants, Zombies_Participants, Traitors_Participants and Group_Messages, User_Messages, Game_Messages.

Should I use some sort of inheritance (i.e. create Participants and Messages tables) or should I leave it as is? If I should create parent tables, how should I go about it?

Also, any other critiques on my schema are welcome! I want to catch mistakes while I am early in the process. The link below is the current schema for my database.

Previous Design

Previous Design

Updated Design

Updated Design

Riley Conrardy
  • 392
  • 1
  • 9
  • 17
  • Inheritance.. probably not: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_table_inheritance But if the tables are very similar, you might think about using only ```participants``` or ```messages``` and add a type to these tables – Islingre Oct 23 '19 at 22:23
  • I have seen `is a` relationships in ER diagrams before, are those relationships assuming using inheritance? – Riley Conrardy Oct 23 '19 at 22:36
  • Well, yes... but perhaps you should not translate everything 1:1 from an ER to the database. I had a look at your model: one table for all the participants seems to be enough. Since messages have connections to different tables... well you can merge them but it is not as obvious as for the participants. If you change your user/group model to a model with only one table (role perhaps), they would look quite the same again.. I guess I would use only one message table (in both cases, group/user or role). – Islingre Oct 23 '19 at 22:43
  • Aside from this, I would not recommend using composite primary keys. Better use an "synthetic" ```id``` column (you may still add ```UNIQUE``` constraints on desired columns and groups of columns). This usually makes connecting tables a bit easier – Islingre Oct 23 '19 at 22:46
  • Interesting I did not realize composite keys are bad practice. I will check that out and probably change my current schema. – Riley Conrardy Oct 23 '19 at 22:58
  • Okay, I updated the design following the recommendations, although I'm still confused about how to merge the `messages` tables. Any other recommendations? – Riley Conrardy Oct 23 '19 at 23:19
  • I guess you need some kind of ```type``` column in your new table ```participants``` indicating of which type (traitor, zombie, assasin) the participant is. This info seems to be lost – Islingre Oct 23 '19 at 23:31
  • 1
    Please reduce your schema to the minimum needed for the actual question and present it **as text**. I don't read comics. – wildplasser Oct 23 '19 at 23:34
  • 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 Oct 24 '19 at 08:50
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image – philipxy Oct 24 '19 at 08:51

2 Answers2

1

Got a bit long for comments, so here's an 'answer'. Composite keys aren't a bad thing. (But I don't use them.) The benefit of a unique synthetic key (identity column or UUID) is that it's stable. You add it once, leave it alone, and never have to update it. Like the old saying goes "smart numbers aren't." But one problem with synthetic keys is that they can obscure problems with the "real" key on the data. Say that you need uniqueness on three fields, more of more of which might change. Okay, that's a good place for a unique, synthetic key as long as you still enforce the uniqueness on the three fields. Postgres is great at this.

A synthetic PK is an implementation convenience, it's less important than your real-world rule. If that wasn't clear, the point is that if, say, three fields must be unique, that needs to be checked. The uniqueness here is based on the real world, as you've modeled it. Put another way, you can bolt a synthetic number/UUID onto the row, and voila! It's unique! But not in a useful way. So, use the synthetic PK, but add a unique index on the composites. This way, if any of the combined values change and violate your uniqueness rule, the engine blocks the insert/update. But you don't have to get into the messy business of reworking a PK which may be used elsewhere as a FK. For some docs, see:

https://www.postgresql.org/docs/current/index-unique-checks.html

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS.

Morris de Oryx
  • 1,857
  • 10
  • 28
  • For a junction table that implements an m-to-n relationship, you *always* want a primary key that is the composite of the foreign keys. – Laurenz Albe Oct 24 '19 at 06:09
  • Agreed....I wasn't looking closely at the example, I was talking about PK construction on a primary table. I've worked in a setting where, no joke, people just bolted a serial number or UUID onto rows and said "no duplicates!" – Morris de Oryx Oct 24 '19 at 08:20
  • I totally agree with your answer, just wanted to add that bit of information/opinion. – Laurenz Albe Oct 24 '19 at 08:31
0

On the question “should I have several *_participant tables or not?”:

The big advantage of having a single table is that you can have foreign key relationships between participants and other entities.

If most of the attributes are the same, use a single table with a type column that has all possible attributes and CHECK constraints to make sure the right ones are set.

If there are many attributes and big differences between the attributes of certain types of participants, you can put these extra attributes into type specific tables that have a foreign key relationship with the common participants table that holds the common attributes.

That latter technique can also be useful if you need foreign key relationships that involve only certain types of participants.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263