0

I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I want to design a small database for my app users. My initial go at this is as follows:

Table design

I am not sure how the single responsibility principle applies to the database world. Should I split this table into 3 tables, one which holds only the Facebook ID, and another for all high score related fields (HighScore, AllTimeHighScore, ScoreDate, ...) and a 3rd for all medal fields, and maybe a 4th table for just the Expert Mode Score, or keep it as is? I know this is probably overkill, but I would like to know the basic principles regarding what can be grouped in a single table.

Note: I would like to take into consideration future proofing. If in the future I decide I want to add a 'platinum medal' field, would that change at all how I would want to design my above table now?

1
  • Think of tables as classes and columns as properties of that class. That's not exactly true, but should help you get started.
    – Jon Seigel
    Commented Mar 6, 2014 at 19:16

1 Answer 1

3

There are a couple of ways you could model this database. There is nothing wrong with the way you have it right now. However, you could normalize the table structure something like:

CREATE TABLE Player (
   facebookId BIGINT NOT NULL, 
   XP int NULL,
   PRIMARY KEY (facebookId)
)

CREATE TABLE Score (
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    score INT NOT NULL,
    scoreDate2 DATETIME NOT NULL,
    PRIMARY KEY (score)
)

CREATE TABLE MedalType (
    medalType INT NOT NULL IDENTITY(1,1),
    medalName VARHCAR(10) NOT NULL,
    PRIMARY KEY (medalType)
)

CREATE TABLE PlayerMedal {
    medalId BIGINT NOT NULL IDENTITY(1,1),
    facebookId BIGINT NOT NULL FOREIGN KEY REFERENCES Player(facebookId),
    medalType INT NOT NULL FOREIGN KEY REFERENCES MedalType(medalType),
    PRIMARY KEY (medalId)
}

This type of structure would be a lot more flexible. To add your "platinum" medeal, you would add a row to the MedalType table. You could then query the number of medals of that type that the player has from the PlayerMedal table.

For the All-Time High Score, use the Score table with the MAX() function to get their highest score.

Two things that I didn't quite model properly here are:

  1. XP -- didn't know what this was, so wasn't sure where to place it.
  2. Using facebookId as the primary key - Wouldn't do this in case you decide your system isn't going to be solely based on Facebook. Would use an unique key, with facebookId as another column in the table.
1
  • Of course. Can you tell I'm not a gamer? :)
    – Shawn H
    Commented Mar 7, 2014 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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