I have an entity consisting of several attributes (can be added, can be removed from entity - and new attributes made as well). I need to be able to store the values - so for example I have a list of Attribute / Value pairs . When assigning Attribute to Machine - the Machine also has a min max value for the Attribute - how can I structure the tables to create the proper relationship. Consider the following tables

  • [Machine Table: MachId],
  • [Machine_EAV Table: MachId, EavId, ?MinVal?, ?MaxVal?, ?Value?],
  • [EAV Table: EavId, Name, TypeId] ,
  • [Type Table: TypeId, TypeName]

In order to store values and have min and max Should I have a Table for the specific dataTypes like this: IntType_Attributes IntId, Machine_EAVId, MinVal, MaxVal, Value

If anyone needs more info I can add that just ask and I will do my best to provide. Note the Machine Parameters are constantly changing .. it needs to be flexible.

I am going to need to present a proper view as well - so I will need to be able create a view from the tables..

  • 1
    also has a min max value for the Attribute And how do you want to use this info? Do not allow to insert the data out of the range? Output this data additionally to the actual value for comparing purposes? something else?
    – Akina
    Jan 14, 2020 at 6:37
  • @Akina - min max value is for two purposes the data entry integrity - can not enter less or more than the amount(I could handle this in the entry screen, but thought if I have the values here it might make more sense. The second purpose is that the property might have a range and not a static value - in which case I need to have that elsewhere. Since I have these constraints - I might need tables for each type of data .. and refer to them that way and also in my intermediate table ..
    – StixO
    Jan 14, 2020 at 14:13
  • 2
    I could handle this in the entry screen Client-side integrity check only and its absense are synonims. From the other side CHECK Constraint cannot refer to another table. So server-side trigger checking in addition to client-side validation seems to be the only option.
    – Akina
    Jan 14, 2020 at 17:25
  • @Akina - I have the idea to setup separate tables for the data type with value, min max as Machine will use those to test a tolerance in range.(this is entered by user ) - which on the entry screen for those values I will set each to have its limits min and max for individual fields so min might be 0 to 15 as acceptable input from user. The machine will use that value as a min - and other similar as the max value. This is the tricky part I need to relate that (what I call the values table with min max and value to the EAV Table. How should I do the relations given the scenario?
    – StixO
    Jan 15, 2020 at 4:38


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.