4

I need to build a UI for entering values to the database. I am stuck with my class design to achieve it.

I need a UI where I am able to enter the name of the system. Each system can have many options. Each option is associated with a TC(Technical chara). Each TC has another set of values which is described as TCset. For instance, let's say Tcset for a TC (TC1) has values: TC1.1, TC1.2, TC1.3. (There other variables describing each row in the TCset, I have mentioned the name property alone for simplicity. Now again coming back to the system, the option defined in each system can have multiple option values. For each option value I should be able to enter a value for the name property in TCset associated with the TC of that option.)

What I would like to achieve would be something similar to this.

Adding a system and defining the Options:

Adding Value for Option and value for SetVal:

Adding Value for Option and value for SetVal

Based on my understanding I have designed the following class diagram. But still I can't find the exact mapping between the Option and SetVal. I am unable to design a class where I could add values for the Tcset in a particular option

public class Lsystem
{
    public int LsystemID { get; set; }
    public string LsystemName { get; set; }
    public virtual ICollection<Option> Options { get; set; }
    public int OptionId { get; set; }

}


public class Option
{
    public int OptionID { get; set; }
    public string OptionName { get; set; }
    public int TCID { get; set; }
    public virtual TC tc { get; set; }
    public virtual Lsystem Lsystem { get; set; }
    public int LsystemID { get; set; }
    public virtual ICollection<OptionValue> OptionValues { get; set; }
}


public class OptionValue
{
    public int OptionValueID { get; set; }
    public string ValName { get; set; }
   //public int TCsetID { get; set; }
    public int OptionID { get; set; }
    public int SetValID { get; set; }
    public virtual Option Option { get; set; }
  //public virtual TCset TCset { get; set; }
    public virtual SetVal SetVal { get; set; }
}

public class TC
{
    public int TCID { get; set; }
    public string TCName { get; set; }
    public virtual ICollection<TCset> TCsets { get; set; }
}

public class SetVal
{
    public int SetValId { get; set; }
    public double Value { get; set; }
    public int OptionID { get; set; }
    public int TCsetID { get; set; }
    public virtual OptionValue OptionValue { get; set; }
    public virtual TCset TCset { get; set; }
}

public class TCset
{
    public int TCsetID { get; set; }
    public string TCsetName { get; set; }
    public string PhysicalUnit { get; set; }
    public int TCID { get; set;}
    public virtual TC TC { get; set; } 

}

Is there something I am missing to get it right. But the current class model doesn't work because foreign key conflicts. That made me add another class SetVal but did not solve my problem. I have tried a lot to get all the relationships right but nothing worked out.

Just for a briefing, the relationships that I have used here:

  System - Option : One to Many
  Option - OptionValue : One to Many
  Option - TC : Many to One
  TC - TCset : One to many
  OptionValue - SetVal : One to One
  TCset - Setval : One to One

enter image description here Edit

The point where i am stuck is generating a table which would take option value id and give an option to add values for the tcset.

That is Under Option Value Ab1, i need a table to give values like the following

Option Value Ab1

ABC1 - 45 ABC2 - 34

Option Value Pq1

PQR1 - 67 Pqr2 - 34 pqr3 - 56

Option Value Pq2

PQR1 - 67 pqr2 - 34 pqr3 - 67

This connection is what i am missing. i realized this when i was trying to make tables the way it was made in the answer

Edit : 2 Adding example

     +--------------------------+
     |  SystemId  | System Name |
     +------------+-------------+
     |     1      |   DCC - 050 |
     |____________|_____________|
     |     2      |   DCC - 070 |
     |____________|_____________|

     ____________________________
     |  TCID      | TCName      |
     |____________|_____________|
     |    1       | Screw       |
     |____________|_____________|
     |    2       |  Motor      |
     |____________|_____________|

     __________________________________________________________
     | TCSetID   | TCsetname|PhysicalUnit | DataUsage | TCID   |
     |___________|__________|_____________|___________|________|
     |    1      | speed    | m/sec       | Prelimina |   1    |
     |___________|__________|_____________|___________|________|
     |    2      | d_0      | mm          | final     |   1    |
     |___________|__________|_____________|___________|________|
     |    3      | d_1      | m           | final     |   1    |
     |___________|__________|_____________|___________|________|
     |    4      | torque   |  mm         | final     |   2    |
     |___________|__________|_____________|___________|________|


     ____________________________________
     |OptionID | SystemID | TCID |OpName |
     |_________|__________|______|_______|
     |    1    |     1    |  1   |Screw  |
     |_________|__________|______|_______|
     |    2    |     1    |  2   |Motor  |
     |_________|__________|______|_______|

     _______________________________________________
     |OptionvalID | SystemID|OptionID | OptionValue|
     |____________|_________|_________|____________|
     |    1       |    1    |    1    |    01      |
     |____________|_________|_________|____________|
     |    2       |    1    |    1    |    02      |
     |____________|_________|_________|____________|
     |    3       |    1    |    1    |    03      |
     |____________|_________|_________|____________|
     |    4       |    1    |    2    |    01      |
     |____________|_________|_________|____________|
     |    5       |    1    |    2    |    02      |
     |____________|_________|_________|____________|

Now the main issue is, i want to give values for the TCsetname in each option. My resulting table would be the following (This is not the database schema)

SCREW

     __________________________________________
     |TCSet |     01   |    02    |    03     | => Option values for Screw
     |______|__________|__________|___________|
     |speed |    12    |    14    |     16    |
     |______|__________|__________|___________|
     |d_0   |    34    |    56    |     56    |
     |______|__________|__________|___________|
     |d_1   |    2     |     5    |     6     |
     |______|__________|__________|___________| 

MOTOR

     ______________________________
     |TCSet |     01   |    02    | => Option Values for the Option Motor
     |______|__________|__________|
     |Torqu |    12    |    14    |     
     |______|__________|__________|

I am looking to correct the datamodel which accepts the last table

27
  • 3
    Can you give a non-technical description of the meaning of each component and their relationships? It's not very clear to me what exactly this class hierarchy or database schema is supposed to model. – Pieter Witvoet Sep 24 '15 at 15:38
  • I need a UI where I am able to enter the name of the system. Each system can have many options. Each option is associated with a TC(Technical chara). Each TC has another set of values which is described as TCset. For instance lets say Tcset for a TC (TC1) has values: TC1.1, TC1.2, TC1.3. (There other variables describing each row in the TCset, I have mentioned the name property alone for simplicity.Now again coming back to the system, the option defined in each system can have multiple optionvalues.... – Vini Sep 25 '15 at 5:19
  • ..... For each option value I should be able to enter a value for the name property in TCset associated with the TC of that option... Hope I could make it more clear.. – Vini Sep 25 '15 at 5:19
  • @PieterWitvoet: I am not sure if this was what you were asking for. It took me 3 days to understand the requirement. This was the most easiest way for me to explain.. – Vini Sep 25 '15 at 5:20
  • 1
    That's still very abstract. Look, your screenshots and code are ambiguous. For example, it looks like you can add new options to a system, right? And options can be enabled or disabled? (but that's not reflected anywhere in the code...) And since each option is associated with a TC, and a TC has a set of allowed values (confusingly split up into a TCSet and SetValue), each option has a corresponding dropdown box - but multiple options can belong to the same TC, so something's not right there... – Pieter Witvoet Sep 25 '15 at 11:28
1

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.

2
  • In my scenario i have only used one/ two attributes for each table for simplicity. i am just now checking the functionalities that needs to be implemented with a smaller database.. but the DB schema would remain the same. – Vini Sep 25 '15 at 13:40
  • But there is something still missing in the model. Please refer to the updated question. @PieterWitvoet – Vini Oct 6 '15 at 11:32
1

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.

2
  • Again you missed something there.. There can be more than one definiton for the primary motor making it to have multiple values for the options.. i think its better i give my real example rather than playing around with "abc"s and "xyz"s.. – Vini Sep 26 '15 at 8:51
  • I will not design the table as such. Because that is what i am doing it wrong. So i would juz give the final output that I would require. Then it would be possible to check with my datamodel that i currently have.. – Vini Sep 26 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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