6

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.

2
  • Do you want the users to be able to add game properties that are custom to their instance?
    – JeffO
    Jun 11, 2016 at 14:35
  • @JeffO no, we (the developers) will be in total control of which properties are needed for which game. Jun 12, 2016 at 14:49

4 Answers 4

9

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.

7
  • Same answer, at the same time. ^^
    – gvo
    Jun 10, 2016 at 15:39
  • 1
    I strongly disagree with your suggestion. You mentioned running the SUM aggregate function on a string column, which should actually contain numbers, but what you don't mention is that it's not the fault of decomposition, it's fault of bad design. You could specify an entity having a number attribute instead of varchar. Your approach could also potentially lead to a table with hundreds of columns where pretty much all of them are going to be nullable, that's not good either and looks like bad design. Decomposition gives the app user the option to introduce his own properties as well.
    – Andy
    Jun 10, 2016 at 17:17
  • @DavidPacker Then your EAV model starts becoming more complicated. Instead of EAV you have "EAVintVstringVfloatVguid..." and your database balloons in size because EAV generates a lot of records and you just made the record a lot larger! (And all of the records will have most of its values as null, so how is this any better than your nightmare scenario? EAV is a bad idea no matter which way you look at it.) Jun 10, 2016 at 18:03
  • You could have separate EAV tables for different types; int, date, string, etc. Let's face it, none of this is ideal. The point of EAV is a way to allow users to dynamically create data types without creating a table field and additional coding. As far as game settings go, even if there are 5000 records with half the fields null is not that much data.
    – JeffO
    Jun 11, 2016 at 14:38
  • 1
    EAV is the crack cocaine of database design. It's ooooh so tempting at first ... then it destroys your life. Jun 12, 2016 at 1:07
5

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.

0

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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.