Will it be an anti-pattern to suffix table name with a numeric id

57 Views Asked by At

Context:

I have a Game table which is a master table and it will hold pre-defined game rows, say:

Game table

Now, I could create a Game_Detail table, that will contain game played state/history/data specific to games like this:

Game_Detail table



Dilemma:

Individually these games will have heterogeneous data, and since I'm not using a NoSQL/Document database, this can't be stored efficiently in a single table. Thus I'm thinking of using the following approach:

Suffix the table name with the foreign key/id of the game. i.e.:

Game_Detail_001 table

Game_Detail_002 table


Some thoughts:

  1. Since code will access only the design-time/config-defined games, no dynamic Table name concatenation will be required, and thus no ugly code or performance concerns
  2. Again, since this all is at design time, ORM will not have a problem. I am using Sequalize with Node.js
  3. This may perform better, as keeping data in same table will help i/o

Extra:

  1. Don't want to use JSON, key/property lookup table for obvious reasons
  2. I believe this is a common scenario and I've tackled this in the past, but I'm not able to think clearly and thus need some help. Specially, If this is an anti-pattern, and will have any problems down the line?
1

There are 1 best solutions below

2
Pércoles Tiago Napivoski On

I think you could do the following:

game
- id PK
- name Text
- ...

player
- id PK
- name Text
- ...

property
- id PK
- name Text

game_detail
- game FK(game.id)
- player FK(player.id)
- property FK(property.id)
- value Real
- PK(game,player,property)