Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Mar 6 at 19:16

1 Answer 1

up vote 3 down vote accepted

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.
share|improve this answer
    
Thank you. XP stands for experience points. –  user26471 Mar 7 at 14:24
    
Of course. Can you tell I'm not a gamer? :) –  Shawn H Mar 7 at 20:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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