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.
DB design: what is a good way to handle properties for game?
database design;properties
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.