Designing my classes with relationships

User story

Based on my current understanding of your program, it's dealing with information about various systems, where different systems can have different properties. Each property has a limited set of possible values. However, users must be able to define new properties and must be able to modify their set of possible values.

Let's assume we'll use it to track spare engine parts, just to make things more concrete. Pretty much every spare part (system) has a name and a manufacturer. For some parts, it's important to know their weight, while for others, we need to know their color. So far, we've got 4 different properties: Name, Manufacturer, Weight and Color (but we'll assume that every part has a name so that's the only property that won't be configurable).

Let's also assume we're only dealing with 2 different manufacturers (Wheelers and BigCorp), only 3 weight classes (Light, Medium and Heavy) and 3 different colors (Red, Blue and Green).

Spare part 'Large wheel' is made by Wheelers and has a Medium weight, but we don't care about its color. Spare part 'Front window' is made by BigCorp and it's Green, but we don't care about its weight.

Database schema

We need to store information about options and their available values (Manufacturer -> Wheelers and BigCorp, Weight -> Light, Medium and Heavy, Color -> Red, Blue and Green). This is what I called 'blueprint data'. It describes what options and values a user can choose from, but does not store any actual system data.

option table:
+----+--------------+
| id | name         |
+----+--------------+
| 1  | Manufacturer |
| 2  | Weight       |
| 3  | Color        |
+----+--------------+

option_value table:
+----+-----------+----------+
| id | option_id | value    |
+----+-----------+----------+
| 1  | 1         | Wheelers |
| 2  | 1         | BigCorp  |
| 3  | 2         | Light    |
| 4  | 2         | Medium   |
| 5  | 2         | Heavy    |
| 6  | 3         | Red      |
| 7  | 3         | Blue     |
| 8  | 3         | Green    |
+----+-----------+----------+

We also need to store information about actual systems, which properties are relevant to them and what values are selected for those properties ('Large Wheel', made by Wheelers, Medium weight, and 'Front window', made by BigCorp, Green color).

system table:
+----+--------------+
| id | name         |
+----+--------------+
| 1  | Large wheel  |
| 2  | Front window |
+----+--------------+

system_property table:
+-----------+-----------+-----------------+
| system_id | option_id | option_value_id |
+-----------+-----------+-----------------+
| 1         | 1         | 1               | // Large Wheel is made by Wheelers
| 1         | 2         | 4               | // Large Wheel has Medium weight
| 2         | 1         | 2               |
| 2         | 3         | 8               |
+-----------+-----------+-----------------+

Code

We can represent this with the following classes:

public class Option
{
    public string Name { get; set; }
    public ICollection<OptionValue> AvailableValues { get; set; }

    // Database-related fields:
    public int ID { get; set; }
}

public class OptionValue
{
    public string Value { get; set; }

    // Database-related fields:
    public int OptionID { get; set; }
    public int ID { get; set; }

    // Parenting:
    public Option Parent { get; set; }
}

public class System
{
    public string Name { get; set; }
    public ICollection<SystemProperty> Properties { get; set; }

    // Database-related fields:
    public int ID { get; set; }
}

public class SystemProperty
{
    public Option Option { get; set; }
    public OptionValue SelectedValue { get; set; }

    // Database-related fields:
    public int SystemID { get; set; }
    public int OptionID { get; set; }
    public int OptionValueID { get; set; }

    // Parenting:
    public System Parent { get; set; }
}

Conclusion

Glancing at the screenshot, you'd also need an IsEnabled property in SystemOption. Physical units and double values are also missing, but it's not clear to me where those should fit in. Still, the above contains only 4 classes as opposed to 6, and the emerging relationships seem to be fairly straightforward:

• The program can contain multiple options, where each option can have multiple possible values.
• The program can also contain multiple systems, where each system can have multiple properties.
• A property refers to a specific value for a specific option.

I've also given different names to a few things, which I hope will clear up some of the confusion.

I'm guessing that what you are trying to do is use your model to describe both the metadata and the data for your system(s).

So, System 1 has several options, such as Primary Motor, Secondary Motor, and Transmission An option has a type (what you are calling TC), so Primary Motor and Secondary Motor would both be Motors, whereas Transmission might be TransmissionType Each type has characteristics: Motor might have horsepower and torque; Transmission might have the number of speed/ratios and clutches.

Now you want to define the values for each option in System 1: Primary Motor has 200hp and 180 lb/ft of torque. Secondary Motor has 80hp and 120 lb/ft of torque. Transmission is 6 speed 1-clutch.

So:

System
___________________
| ID | System_Name |
|____|_____________|
|  1 | System1     |
|____|_____________|

Option_Type (TC)
_________________________
| ID | Name              |
|____|___________________|
|  1 | Motor Type        |
|____|___________________|
|  2 | Transmission Type |
|____|___________________|


Type_Characteristics (TC_SET)
_____________________________________________
| ID |  TC_ID  | NAME           |  UNIT_TYPE |
|____|_________|________________|____________|
|  1 |  1      | Horsepower     |   HP       |
|____|_________|________________|____________|
|  2 |  1      | Torque         |   lb/ft    |
|____|_________|________________|____________|
|  3 |  2      | Clutch Count   |   clutches |
|____|_________|________________|____________


OPTION
________________________________
| ID | Name            |  TC_ID |
|____|_________________|________|
|  1 | Primary Motor   |   1    |
|____|_________________|________|
|  2 | Secondary Motor |   1    |
|____|_________________|________|
|  3 | Transmission    |   2    |
|____|_________________|________|


Option_Values
_______________________________________
| ID | OPTION_ID |  TC_SET_ID | VALUE  |
|____|___________|_____________________|
|  1 |      1    |      1     |   200  |
|____|___________|____________|________|
|  2 |      1    |      2     |   180  |
|____|___________|____________|________|
|  3 |      2    |      1     |    80  |
|____|___________|____________|________|
|  4 |      2    |      2     |   120  |
|____|___________|____________|________|
|  5 |      3    |      3     |    2   |
|____|___________|____________|________|

What won't happen here is the database enforcing that the Primary Motor option only has values for TC_SET_ID values 1 and 2--that must be enforced in your code instead. However it does give you the means to build your UI:

The user picks a system, a form gets built out for each option, where it asks for a Option_Value to be entered for each Type_Characteristic associated with the Option_Type associated with that option.