CODEWITHSUNDEEP
databasepostgresqldatabase-design
Ryan Ritten
Ryan Ritten

Reputation: 35

1:1 Relationships. Split into more than 1 table? Bad?

I am creating a mobile game where I am optimistically hoping i'll have millions of players.

I have created a users table that currently has roughly 8 columns (ie. userid, username, password, last_signin, etc)

For every user I'll also need to record the amount of in-game currency they have (ie. gold, silver, gems, etc).

This is a 1:1 relationship (a user will only ever have 1 value defining how much gold they have).

I am no database expert (which is why I am posting here). I worry If I added the gold, silver, gems, etc as new rows in the users table that the users table will be hammered with a crazy amount of queries per second. Everytime someone in the game finds more gold, more silver, logs in, creates an account... the users table will be accessed and/or updated.

Would it be smarter to add the gold, silver, and gems as columns in a new table called "resources" that had the following columns : userid, gold, silver, gems. This new table would have the exact same number of rows as the user table since there is a 1:1 relationship between users and resources. I'm wondering if those queries would be faster since the database data is split up and not all queries would go to the same table.

Clearly to me it seems better to put it all in 1 table since they are 1:1.... but It also seemed like a bad idea to have the majority of the games data in 1 table.

Thanks for any advice you can give!

Ryan

Upvotes: 2

Views: 1065

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

There are plenty of cases where good design calls for two tables in a 1:1 relationship with each other. There is no normalization rule that calls for decomposing tables in this manner. But normalization isn't the only handle on good design.

Access traffic is another handle. Your intuition that access to resources is going to be much more frequent than access to basic user data sounds credible. But you will need to check it out, to make sure that the transactions that access resources don't end up using basic user data anyway. It all boils down to which costs more: a fat user table or more joins.

Other responders have already hinted that there may come a day when the 1:1 relationship becomes a 1:many relationship. I can imagine one. The model of the game player gets expanded where a single user can get involved in multiple distinct instances of the game. In this case, a single user might have the same basic user data in all instances, but different resources in each instance. I have no way of telling if this is ever going to happen in your case. But, if it does, you're going to be better off with a separate resources table.

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324375

This is a 1:1 relationship (a user will only ever have 1 value defining how much gold they have).

... for now ;)

I am no database expert (which is why I am posting here). I worry If I added the gold, silver, gems, etc as new rows in the users table

New columns?

Would it be smarter to add the gold, silver, and gems as columns in a new table called "resources"

Probably, because:

  • You'll be doing smaller writes when you update the frequently updated part, without rewriting less-modified user data

  • It makes it easier to audit changes to the user data

Upvotes: 0

Daniel H.J.
Daniel H.J.

Reputation: 448

It really depends on your game design, how big your database is, and how you might expand your database in the future. I would put the resources in a separate table with a foreign key pointing to the user id because:

  1. You can keep the user table slimmer for easier maintenance/backup.
  2. Simple 1-to-1 JOIN operation between two tables doesn't take much more resources than having everything in the same table, as long as you have proper indexing.
  3. By keeping your tables separated, you are practicing separation of concerns; multiple people can work on different stuff without having to worry about affecting other tables.
  4. Easier to expand. You may want to add other columns such as birth_date, region, first_name, etc. that are more relevant to users' personal info to the users table in the future. It will be confusing if columns of different purposes are stored together. (In PostgreSQL you can't simply arrange column order though you can create Views for that.)

Upvotes: 0

Related Questions