Beginner table design

Posted on

Question :

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?

Answer :

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:

   facebookId BIGINT NOT NULL, 
   XP int NULL,
   PRIMARY KEY (facebookId)

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

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

CREATE TABLE PlayerMedal {
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *