Travis T Travis T - 1 year ago 71
SQL Question

Creating a score sheet in SQL

On my website, I have a score sheet that the user fills out. They will USUALLY have 10 items on a score sheet and each item will have its own score. I am wanting to design this in the best way for future expandability. What If I want to have more or less items on the score sheet in the future?

Here is what I have now:

public class Scoresheet712Item
public int ScoresheetItemId { get; set; }
public int ScoresheetId { get; set; }
public int DistanceAway { get; set; }
public int score { get; set; }

Is it better to do this or is it better to have all 10 scores and distances on the same row? I would rather have them in the same row because I can pull that one row directly in as a model. I originally thought to do it this way so I could easily vary how many slots there are on the score sheet, but it doesn't seem like there is much benefit really, specifically because I am using MVC development and I will always need the entire score sheet.

Please help, can I have all the data in one row for a score sheet and that be good practice?

Here is what I am trying to propose, it would be a little different though because each score DOES have a different purpose.:

public class Scoresheet
public int ScoresheetId { get; set; }
public int DistanceAway1 { get; set; }
public int score1 { get; set; }
public int DistanceAway2 { get; set; }
public int score2 { get; set; }
public int DistanceAway3 { get; set; }
public int score3 { get; set; }
public int DistanceAway4 { get; set; }
public int score4 { get; set; }
public int DistanceAway5 { get; set; }
public int score5 { get; set; }
public int DistanceAway6 { get; set; }
public int score6 { get; set; }
public int DistanceAway7 { get; set; }
public int score7 { get; set; }
public int DistanceAway8 { get; set; }
public int score8 { get; set; }
public int DistanceAway9 { get; set; }
public int score9 { get; set; }
public int DistanceAway10 { get; set; }
public int score10 { get; set; }

Like this?

public class Scoresheet712
public int Scoresheet712ID { get; set; }
public virtual ICollection<Scoresheet712Item> Scoresheet712Items { get; set; }

Answer Source

I am inclined to agree with @Stephen Muecke to be flexible and extendable you really need two sql tables.

public class Scoresheet
    public int ScoresheetId { get; set; }
    public string ScoresheetName { get; set; }
    public ICollection<ScoresheetItem> ScoresheetItems { get; set; }


Public class ScoresheetItem
    public int ScoresheetItemId { get; set; }
    public int Score { get; set; }
    public int Distance { get; set; }
    //Navigation properties
    public int ScoresheetId { get; set; }
    public Scoresheet Scoresheet { get; set; }

This will let you build a new Scoresheet as needed with as many or as few items as you want. The example below shows the usage.

//Create a new Scoresheet
Scoresheet scoresheet712 = new Scoresheet()
    ScoresheetName = "Score Sheet 712",
    ScoresheetItems = new List<ScoresheetItem>()
//Add a ScoresheeItem to Scoresheet
scoresheet712.ScoresheetItems.Add(new ScoreSheetItem
    Score = 10,
    Distance = 150

Remember that on your View you do not need to use your Data Model you can always add a Data Transfer Object (Scoresheet_DTO) and make the flat structure for your score sheet if it really does make displaying it easier/better, just be sure to use nullable integers.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download