ASP How to organize a database where each item has multiple known values?
<p>So, I have a set of data that represents ball objects. Each ball is predefined, i.e. there are a know number of ball objects, say 3 total. Each ball has a name (string). Each ball can either be Small, Medium, or Large (enum), and can be either Green or Red (enum). For each ball of each type of each color, I want to have a history of its last 10 locations (arbitrary type for this example).</p>

<p>So, initially, my thought was to create a table of ball objects like so:</p>

<pre><code>public class Ball {
    public int Id { get; set; }
    public string Name { get; set; }
    public Color Color { get; set; }
    public Size Size { get; set; }

    public virtual ICollection&lt;BallHistory&gt; History { get; set; }
}

public class BallHistory {
    public int Id { get; set; }

    public DateTime Date { get; set; }

    public int BallId { get; set; }
    public virtual Ball Ball { get; set; }
}
</code></pre>

<p>However, this means that I would have a lot of repeats of ball data (such as Name), because Name would be repeated for each size and each color, like the following:</p>

<pre><code>Ball Table:
Id   Name     Color   Size
0    MyBall   0       0
1    MyBall   0       1
2    MyBall   0       2
3    MyBall   1       0
4    MyBall   1       1
5    MyBall   1       2

History Table:
10 items with BallId = 0,
10 items with BallId = 1,
etc.
</code></pre>

<p>Now, because of the way the classes are set up, the History table will contain 10 entries for each ball, where each entry stores the Id to the Ball it is a history for. The issue really lies in how to best create the classes to minimize redundant data. Since both color and size are pre-defined, I was wondering how best to separate this data out into its own table, if possible. An example would be duplicating the History property in Ball, so that there was a SmallHistory, a MediumHistory, and a LargeHistory. Or, maybe it's better to somehow separate out the Color and Size, and instead of repeating the Name property, only an integer would be repeated for each different color and size (which would be less data taken up).</p>

<p>Are there any thoughts on this? If anything needs more clarification, let me know. In this example, there are 3 balls, each has 2 color and 3 size options (6 options total), and for each specific ball with specific options, there are 10 history items. So, in this example, there are a total of 18 rows in the Ball table, and 180 rows in the BallHistory table, if I'm not mistaken. In my real application, the number of balls will be around 1,000, with 7 colors and 2 size options, so a total of 14,000 unique balls, each with 10 history entries, for a total of 140,000 history entries. Of course, if done the way mentioned at the top, that's a LOT of repeats of the Name property.</p>