Entity Attribute Value Model datatypes (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
Hi All -

As I am designing an EAV modelled db for mfg inspection data entry, it occurs to me that it may be necessary to store all values as a string datatype. Is that correct?

It works for me because the vast majority of reporting requirements will only require frequencies of occurrence rather than mathematical calculations.

I'm just checking with you all in case I am missing something.

Thanks,

Tim
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
21,358
Hi Tim. I could be wrong but if you use a EAV model, the attribute value column, I think, will have to stay Text. Now, if you have a need to do calculations on those values, you could potentially add another column for the data type.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
Hi Tim. I could be wrong but if you use a EAV model, the attribute value column, I think, will have to stay Text. Now, if you have a need to do calculations on those values, you could potentially add another column for the data type.

That's what I thought. It works. Very, very few - if any - mathematical calculations. There are some date calculations but the dates are stored "above" the EAV portion of the db.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:46
Joined
Apr 27, 2015
Messages
6,281
At the risk of sounding ignorant, what is EAV?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:46
Joined
Jan 20, 2009
Messages
12,849
It can be a good idea to separately manage the measurement unit too rather than storing it as part of the measurement. This way if you do need to apply maths, you aren't faced with parsing the text value.

Another table stores which measurement unit applies to the attribute. Some attributes such as periodicity for example, might have multiple possibilities (second, minutes, hours, days, months. In this case you would store the variation of ID for the unit in the record too.

In some cases, particularly with metric units, you might have a standard unit and store the multiplier with the record. Storing an integer with another column for the multiplier rather than a decimal number with a lot of zeros in it.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
It can be a good idea to separately manage the measurement unit too rather than storing it as part of the measurement. This way if you do need to apply maths, you aren't faced with parsing the text value.

Another table stores which measurement unit applies to the attribute. Some attributes such as periodicity for example, might have multiple possibilities (second, minutes, hours, days, months. In this case you would store the variation of ID for the unit in the record too.

In some cases, particularly with metric units, you might have a standard unit and store the multiplier with the record. Storing an integer with another column for the multiplier rather than a decimal number with a lot of zeros in it.

Aye. I'm getting a clearer picture of what "metadata" is and just how much may be needed and how to utilize it.
 

Lightwave

Ad astra
Local time
Today, 16:46
Joined
Sep 27, 2004
Messages
1,521
I like EAV models for storing information about entities the attributes of which are not particularly predictable.

I will typically have the entity as a FKID to the parent. Attribute might be a lookup and then Value will typically be largish text and sometimes even a memo. I quite like it for storing attributes about people. Multiple mobile phones some people have middle names others don't etc etc. It is good for user based systems it reduces the need for contstantly adding fields to some tables.

If I need this in a more table format then I can pivot on the entity and attributes or a selection of the attributes.

It is a regular topic in SQL Server forums as to how efficient it is to run queries on EAV structures.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
I like EAV models for storing information about entities the attributes of which are not particularly predictable.

It is a regular topic in SQL Server forums as to how efficient it is to run queries on EAV structures.

It does appear to require longer SQL statements and multiple JOINS. Not nearly as straight-ahead as traditional but I am even thinking of storing oft-used SQL boilerplate in a table to take a criteria or multiple criteria from the user in an attempt to minimize the pain. :) Might even be able to parse multiple SQL table values together so they don't have to be rewritten anywhere. Then call them when needed. Conceptually, kind of like global variables. It's my grand design. I am 100% certain I'm not the first to think of this but still kind of proud it crossed my mind.

We'll see how that works out.

:)
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:46
Joined
Jan 23, 2006
Messages
15,364
Tim,

Can you post a copy of the database (only some records) as you add/test/develop features? It helps with context and is an interesting topic that doesn't seem to have a lot of examples.
Good luck with your project.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
I will typically have the entity as a FKID to the parent.

This is an interesting sentence that I am having to re-read several times in context of the rest of your post. Trying to wrap my mind around it.

Care to expound/describe a little further? An example maybe?
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
Tim,

Can you post a copy of the database (only some records) as you add/test/develop features? It helps with context and is an interesting topic that doesn't seem to have a lot of examples.
Good luck with your project.

I would be happy too. I've only recently revisited adding an EAV schema to the existing DB. It ends up that my inspections areas have more overlap than I thought. I've discovered this as I have been beta-testing the data entry process with the DB that I was very happy with until I realized I was missing a mountain of potentially very useful info because my traditional design was not robust enough to capture the real dynamics of a typical work day in the shop.

So.... at the moment, the DB is in "Frankenstein" mode before the the lightning came down. :)

But as soon as I work out exactly what the metadata tables are (thank you Pat Hartman for the major jump-start), I'll post the db.

I think it is important to note that the article that DBGuy posted has been extremely helpful also. Very clearly talks about an EAV model nearly always incorporating a branch of true normalized rdbm as well. They support each other. But more on that later.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
I like EAV models for storing information about entities the attributes of which are not particularly predictable.

My number one reason for attempting to remodel the db to incorporate an EAV sub-schema is akin to what you mention here. What is "not particularly predictable" for our needs is - given that this db is for part and product inspections in a steel manufacturing plant making custom products - we don't know always know if we're going to need to inspect one feature or two dozen features. Also, different products have some shared characteristics that must be measured and at the same time have critical non-shared features that need to be inspected and recorded.

My hope is that using the EAV model for the inspection branch of the DB will allow for a more robust data collection process - - - very much like the doctor/patient intake exam example in the aforementioned article.
 

Lightwave

Ad astra
Local time
Today, 16:46
Joined
Sep 27, 2004
Messages
1,521
This is an interesting sentence that I am having to re-read several times in context of the rest of your post. Trying to wrap my mind around it.

Care to expound/describe a little further? An example maybe?

I think your choice of the EAV is right on the money. The SQL Server types that I've come across have disliked it in certain circumstances because they have been focused on applications that are externally facing with 1000 of concurrent occasional users that have little or no focus on the system and every millisecond slower in returning values can result in lost user attention. I think with internal highly focused and educated employee users that are using the system every single day it is more important the system has the flexibility to allow them to keep detailed and accurate notes even if it slows down the initial pickup by users. The attention and focus of the users is much more channeled as they will be using it every day they quickly become very familiar with the UI.


I tend to do EAVs in two formats one being slightly more tied down than the other.

1st type
Three tables
tblEntities (in your case engineering product)
tblEntityAttributeValue ( with entity field being integer and being referentially linked to tblEntities - thats the FKID I am talking about
Attribute itself being a lookup referenced to tblAttributeLookup
Value being free form
tblAttributeLookUp

This structure encourages consistent definition of attributes (depending on how widely you give create access to the tblAttributeLookup). And helps minimise similar attribute variations
eg
Colour
Color

Remove access to tblAttributeLookup and enforce only allow options within lookup and you can really limit / frustrate your users...:)

2nd Variation
The alternative is the same but without the tblAttributeLookup and just allow Attribute in the Lookup table to be free form text rather than a lookup. This can be preferable in some cases where the attribute can give some background to the value.

With engineering products I suspect the first 3 table option will be slightly better.

I quite often use the more free form for storing web links.


Another alternative that is often used but not always called EAV is splitting into tables relating to the 'native' data type eg dates / times / numbers / money etc...
To my mind bank statements are like that my bank statement has little more than three columns description amount and date... Clearly in the background the entity is probably my customer number but it is being split into data type in this case money. That is almost exactly an EAV + date structure. From my above variants this would be a EAV of the 2nd variant as the attribute definition is very free form. We are really getting into the finer points of relational design I have called it EAV + date but it could be called EAVV but then what is a child table but an entity and then a number of values E+(V)n

So a EAV table where values are limited to dates.
An EAV table where values are limited to numbers (decimal or otherwise).
An EAV table where values are limited to text.

this can be useful when you have a stream of values that are extremely important to record accurately eg dates / times / moneys... and where you frequently need to do calculations on the subsequent information.
 
Last edited:

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
...they quickly become very familiar with the UI.

1st type
Three tables
tblEntities (in your case engineering product)
tblEntityAttributeValue ( with entity field being integer and being referentially linked to tblEntities - thats the FKID I am talking about
Attribute itself being a lookup referenced to tblAttributeLookup
Value being free form
tblAttributeLookUp

This structure encourages consistent definition of attributes (depending on how widely you give create access to the tblAttributeLookup). And helps minimize similar attribute variations
eg
Colour
Color

With engineering products I suspect the first 3 table option will be slightly better.

....and my ultimate Big Question: how do I design a form that accommodates this design? Specifically: how do I design a form that allows one of our inspectors to make one to many multiple entries during an inspection? I can't see a form that has an infinite amount of combo and text boxes.

I've struggled with this for awhile - long after finally comprehending the underlying table structure. How can I design an inspection data entry form that allows the inspector to select one attribute to record a value and then decide he wants to record a value for some unknown amount of additional attributes? What does that physically look like?

The only way I can see doing it is to have a form/subform or secondary popup form called from the main form where the Record Source for the main form is tblEntities and the record source for the secondary form is tblEntityAttributeValue. The main form would record the general entity data and have a button to click to open the secondary form. The secondary form would have a cbo on it and a text box. The cbo would point tblAttributeLookUp. User makes a choice from the cbo and sets the attribute then the user places a value in the textbox. A Save button is clicked on the secondary form and the data is recorded in tblEntityAttributeValue at which point the popup closes and the user is returned to the Main form and since the Main form has not been requeried a new Attribute can be chosen for inspection.

But that's only my imagination...........how do you approach these things?

And I would definitely utilize something closer to the first 3 table model because- yes - there is a pretty great need for consistency between and among attribute names. Using the third table as a lookup is vital. Fortunately, the list of what might need to be inspected/recorded is predictable. Rather, it is the number of attributes required to be measured that is less predictable.
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
I think with internal highly focused and educated employee users that are using the system every single day it is more important the system has the flexibility to allow them to keep detailed and accurate notes even if it slows down the initial pickup by users.

Exactly the case. There are 3 - 5 of us who will be using the DB tool and the ability to collect dynamic data and observations are the entire value of the thing (much, much more than performance).
 

Zydeceltico

Registered User.
Local time
Today, 12:46
Joined
Dec 5, 2017
Messages
843
Another alternative that is often used but not always called EAV is splitting into tables relating to the 'native' data type eg dates / times / numbers / money etc...
To my mind bank statements are like that my bank statement has little more than three columns description amount and date... Clearly in the background the entity is probably my customer number but it is being split into data type in this case money. That is almost exactly an EAV + date structure. From my above variants this would be a EAV of the 2nd variant as the attribute definition is very free form. We are really getting into the finer points of relational design I have called it EAV + date but it could be called EAVV but then what is a child table but an entity and then a number of values E+(V)n

So a EAV table where values are limited to dates.
An EAV table where values are limited to numbers (decimal or otherwise).
An EAV table where values are limited to text.

this can be useful when you have a stream of values that are extremely important to record accurately eg dates / times / moneys... and where you frequently need to do calculations on the subsequent information.

That's really intriguing. If all of the Like-Types are recorded in a table containing nothing but that type, one would think query calculations and comparisons would be much simpler to write and likely more efficient in a big dataset.

We will have a wee-small dataset and virtually no calculations other than date calculations and frequency of observation types of things. :) But I am always intrigued by becoming familiar with alternative means of using a tool (table design in this case).

Thank you for the very helpful post reply!

Tim
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:46
Joined
Jan 20, 2009
Messages
12,849
Also remember that the Attribute itself can be compound, using two or more fields, potentially reducing the number of unique attributes.

The allowed sub-attributes for the main attributes would be stored in an attribute relationship table.
 

Users who are viewing this thread

Top Bottom