DB design: what is a good way to handle properties for game?

Question

I must persist handful of properties for several games in relational DB. No other type of DB can be used. Number of games will be around 4-5, and number of properties is (for now) kind of small, but maybe it will grow later. It is almost guaranteed that all games will have same properties (same "keys" to say, not same values). My preferred desing for now is this:

CREATE TABLE GamesProperties
(
  propertiesId NUMBER(38,0) NOT NULL,
  gameId VARCHAR2(16 CHAR) NOT NULL,
  cancelPeriod NUMBER(4,0) DEFAULT 5 NOT NULL,
  prizeName VARCHAR2(16 CHAR) NOT NULL,
  CONSTRAINT PK_TGP PRIMARY KEY (propertiesId),
  CONSTRAINT UC_TGP_gid UNIQUE (gameId)
);

But a coworker said that in my design we would have to alter table every time we need to add new property (which I agree), and that he would prefer to see something like this:

CREATE TABLE GamesProperties
(
  propertiesId NUMBER(38,0) NOT NULL,
  gameId VARCHAR2(16 CHAR) NOT NULL,
  propertyName VARCHAR2(16 CHAR) NOT NULL,
  propertyValue VARCHAR2(16 CHAR) NOT NULL,
  CONSTRAINT PK_TGP PRIMARY KEY (propertiesId)
);

In his solution we wouldn't have to alter table at all in the future, but we lose information about datatype of a property which I think is a major downside.

I would like to hear what are some other pros and cons for each solution so I can make informed decision about the design we'll use.

Answer 1

Your coworker's design is a well-known antipattern known as "entity-attribute-value". It looks really attractive at first due to its flexibility, but experienced developers know it's a very bad idea because it eliminates all of the advantages of using a relational database in the first place.

When you have a (normal-style) table that describes the attributes of your data objects, the database engine knows all about the object. It knows what the data types of each member is. It knows (via foreign keys and other constraints) how the data is supposed to relate to other data types, and keeps it from growing corrupt. It knows (via indexes) which members are critical for lookup and is able to optimize queries based on their value, and so on.

EAV throws all of this out the window. You have no distinct data types; everything is the type of the Value column (probably VARCHAR), which can have ramifications beyond the immediately obvious. (Have fun running a SUM of values that are supposed to all be numbers when they're all strings, for example...) You have no referential integrity, because you can't require values of one column to match another column when all you have is a generic "value" column that everything gets thrown into. Generating non-trivial queries that would require joins becomes a convoluted mess of self-joins that no one is able to understand, and the larger and more complex the system becomes, the more nightmarish it gets to try to write queries.

Yes, using normal-style tables requires a bit more setup work up-front, but that's work with a purpose: it ensures that you have everything set up correctly so that actual usage of the database will run smoothly. Considering that you'll only have to set things up once, but then use them repeatedly, trying to avoid setup costs by going to EAV (which makes usage more difficult) is a serious case of premature optimization!

Stick with your plan, not your coworker's.

Answer 2

But a coworker said that in my design we would have to alter table every time we need to add new property (which I agree)

I agree too. What's the issue exactly? When you add new features you sometimes have to modify the database. Sounds fair enough to me, I know how to deal with that easily.

In his solution we wouldn't have to alter table at all in the future, but we lose information about datatype of a property which I think is a major downside.

The major downside with this model (sometimes named Entity-Attribute-Value) are the following :

• Query becomes complex. Even worse when you have to deal with multiple data types.
• You can’t make mandatory attributes
• You loose referential integrity

It's known as an anti-pattern (I recommend reading the SQL Antipatterns book, it's referenced within it).

So needless to say, I would advise you to keep your original solution. And if it's that painful for you to alter a table when you add a new feature, I think there is another bigger issue somewhere else.

Answer 3

The EAV design is a way to allow users/admins a way to create custom fields without requiring additional coding for each item. They can be read and applied dynamicall. They're more suitable for lookup type values. There's nothing in your description about your app that needs this functionality, so you coworker is using a bad design.

If creating fields in a database is that difficult to code, you should use something like and ORM to take care of this for you.

Answer 4

How about this:

+-----------+       +------------------+       +-------------------+
|           |       |                  |       |                   |
|           |       |                  |       |                   |
|   Game    |       |    GameProperty  |       |   Property        |
|           +------->                  <-------+   ID (PK)         |
|   ID (PK) |       | *  GameID  (FK)  +       |   Name            |
|   Name    |       | *  PropertyID (FK)       |   DataType (FK)   |
|   ...     |       |    ValueID       |       |                   |
|           |       |                  |       |                   |
|           |       |                  |       |                   |
|           |       |*PK               |       |                   |
+-----------+       +-+-----+------+---+       +------^------------+
                      |     |      |                  |
                      |     |      |                  |
 +--------------------v--+  |      |                  |
 |                       |  |      |           +------+------------+
 |  PropertyNumberValue  |  |      |           |                   |
 |  ID                   |  |      |           |   RefDataType     |
 |  Value (Number(x,y))  |  |      |           |                   |
 |                       |  |      |           |   ID              |
 |                       |  |      |           |   Name            |
 |                       |  |      |           |   ...             |
 |                       |  |      |           |                   |
 |                       |  |      |           |                   |
 +-----------------------+  |      |           |                   |
                            |      |           +-------------------+
                            |      |
                            |      |
  +-----------------------+ |  +---v------------------------+
  |                       <-+  |                            |
  |  PropertyStringValue  |    |    PropertyDateTimeValue   |
  |  ID                   |    |    ID                      |
  |  Value Varchar(x)     |    |    Value DateTime          |
  |                       |    |                            |
  |                       |    |                            |
  +-----------------------+    +----------------------------+

Note: GameProperty.ValueID references one of 3 tables based on Proerty.DataType. This allows you to keep the datatype of the data out of varchar DB fields though slightly complicates the retrieval of the value.