1

Design 1:

database design 1 image More tables. Better normalization. Data clean/better grouped together.

Questions 1: If a user is registering an account, doesn't that do too much queries?

INSERT INTO player ()...

INSERT INTO player_device ()...

INSERT INTO player_gameprofiledata ()...

INSERT INTO player_personaldata ()...

That are 4 queries. Updating user information can lead to the same amount of queries again.

Questions 2: Is it a bad idea to merge table player_personaldata into player (like in design 2)?

Questions 3: The relation between player and player_device, is this a 1:n or a m:n?


Design 2:

database design 2 image Less tables, but mixed data.

Difference:

  • player_device and player_personaldata fields are in the player table
  • field playername is in table player instead of in table player_gameprofiledata (simplifies login)
  • no track of which device is currently in use through a table player_devic

Question 4: Sufficient normalization or bad design, even if no heavy data-redundancy occurs?

orbitkorbi
  • 45
  • 5
  • Please [use text, not images/links, for text](https://meta.stackoverflow.com/q/285551/3404097), including tables & ERDs. – philipxy Jan 22 '19 at 00:55
  • Possible duplicate of [How do you determine how far to normalize a database?](https://stackoverflow.com/questions/47711/how-do-you-determine-how-far-to-normalize-a-database) – philipxy Jan 22 '19 at 00:58

1 Answers1

0

Both your designs are equally normalized.

I usually avoid 1:1 relations. In your case it would even be a tad dangerous, because a DBMS finds it hard to guarantee real 1:1 relations as between player and player_gameprofiledata. It is easy to guarantee 1:{0,1} relations, i.e. a player has no more than one player_gameprofiledata. That would be done with a simple foreign key. But to guarantee that for each player exists a player_gameprofiledata and vice versa, there would have to be a foreign key from table A to table B and back, which could only be done with deferred constraints that not every DBMS provides.

So keep your database simple. A player is a player with a playername, and if the database is just about one single game, then the player also has one game setting etc. One table.

(There are exceptions, e.g. different access rights to the tables or different programmers working with them. So in my opinion: Have 1:1 tables when really necessary for some reason, otherwise avoid them.)

Answer to question 1: Don't worry. Four inserts that only happen when a player first registers, is not much. And updates don't usually affect multiple tables. However, you have more tables than necessary in your first design. You can (and should in my opinion) keep it simpler.

Answer to question 2: No. Merging the 1:1 related tables is actually a good idea.

Answer to question 3: One player has/had many devices. One device belongs to one player. So it's 1:n. If you had a device table instead and player_device would link players and devices, it would be m:n. That design could help avoid possible mistakes where one player had android version 4.4.1 and another 4.4.01. So I suggest adding a device table. Or, is the device even important. Or a mere information? Your table suggests that you are very much interested in all those devices. If that is the case, then do it thoroughly. If not, then you may be fine with simply adding current device attributes to the player table again, as you are doing in your second approach. (You may add a player_device_history table, that you fill every time a players device changes, so you have the history in case you want it some time, but that table is more a logging table rather than an actual table that you need in daily use.)

Answer to question 4: Both designs are normalized and fine. I'd go with the second design. If you might want to use this database for multiple games in the future however, then you should take this into account already.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thanks. Won't to much columns in a table exceed some limits (e.g. storage)? – orbitkorbi Jan 22 '19 at 15:33
  • No. At least not within sensible limits. I've worked with tables of more than hundred columns without any problems. But there may be limits. See this for MySQL as an example: https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html, and this for Oracle as another example: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/logical-database-limits.html#GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76. Both DBMS support hundreds of columns and so will others. – Thorsten Kettner Jan 22 '19 at 17:27