Creating a score sheet in SQL

Question

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 1

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; }
}

And

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.

UPDATE MANY-TO-MANY RELATIONSHIP

For a many-to-many relationship if setup correctly in code it will automatically create the joining table. The Scoresheet class would remain the same but the ScoresheetItem class would have a small change to the Relationship/Navigation properties.

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

For further help I would recommend taking a look at this Entity Framework Tutorial