Database design problem (or question)

I'm designing a database with three开发者_运维技巧 tables: user, game and (probably) register. I want to represent that a user can be registered on zero or more games.

I use table register to register a user in a game.

I also need to store the score of the user in a game.

My question is:

Can I add a column called score in table register? Is it a good design?

Thanks.

Yes it is an ok design. But you should probably change the naming.

user:
    userid
    username

game:
    gameid
    name

usergame:
    userid
    gameid
    score

What you're describing is a many-to-many relationship, using REGISTER as the cross-reference table:

USER
UserID

GAME
GameID

REGISTER
UserID
GameID
Score
[+ registration info]

Sounds okay, though if either game-related or registration-related info gets more complicated you'll probably split it in two eventually.

Edited to add: Often you'll end up wanting to save game history ("average score", "last 5 games", "fastest time", etcetera). Then you have a one-to-many relationship between REGISTER and a GAME_HISTORY table.

Also, I agree with the answer that said calling it a 'registration' table will be confusing if it contains non-registration info.

You can put a user id and a game id into the register table, along with with a score. This will allow you to have a user registered for many games, each with a score. I think that will probably do what you want, if I understood your question correctly.

I imagine, that you are going to use register table to avoid many-to-many relation between game and user. If you want to keep score of particular user in particular game, I think that's good solution.

If it's a table that keep track of user score in different games: sure, you can have that column. But perhaps call it 'score' instead? (Unless you need some other information there, then perhaps you should use a fourth table)

I think you should consider creating a "relationship" table.

UserId | GameId | Score

UserId and GameId would be the primary key as well as each being foreign to the User and Game tables, respectively.

It is probably an appropriate design, as long as each register can have at most one score. If registers might have no scores, then that would correspond to a value of NULL. If this might be a significant proportion of rows in the table, then another table to hold scores might arguably be appropriate.

In an app I made, I had a similar situation, and because I wanted to add "high-score table" functionality (which necessitates ORDER BY queries on the score column), I ended up with quite a lot of indexes on my "register" table (more than one just for this purpose, because I had to have different indexes for different types of game). In the end I made a new table for scores, even though most registers do have an associated score, in order to make maintenance of these indexes less of an issue.

If the goal of your database is analytics then you'll also consider this:

Keep in mind that you'll be doing an Outer Join unless you include a row in the game table for 'no games registered' and put that as a default in the usergame table. You can delete that "non-registration" with the first registration... and add it back if the last registration is ever deleted.

This will make your reports inner joins and finding all the users without registrations is simple and obvious.