Entity Attribute Value Model

[Zydeceltico]

Hi All -

Update on my QC DB - and some rethinking.

I have been using the beta-DB for inspections and trying to run reports and am seeing that my model - while it does work very well for most day-to-day needs - may not be the most efficient for reporting on the inspections performed.

Because I have my parts, final products, and assembly components tables setup pretty much as an entity-attribute model microcosm - I kind of get it. My tblassemblycomponents is setup like this:

ID     FinalProdID     PartID
1              1               13
2              1               56
3              2               87
4              2               113
5              2               56

and so on and so forth........... and it works very, very well for my needs....but it is a static list meaning it rarely if ever changes and rarely if ever has anything added to it.

I am beginning to believe that something similar would be a better model for a great portion of my inspection requirements AND inspection types as I want to ultimately be able to report on all inspections (of all types and all criteria) of a specific job..... Unlike my parts/products model described above, the real-world on-the-floor issues that need to be recorded in the inspection portion of the DB are highly dynamic in that there is "always something new" or issues that are niched to only specific parts or processes.

Let me reiterate: " I am beginning to believe that something similar......"

And there's the nut of the problem: I have no idea what that may look like as a model and I also don't know what the requisite data entry forms would "look like" ..........

The reason I am beginning to think this is that as I use the DB in its current form, I (we) keep coming across more and more conditions with parts and assemblies and process that really need to be recorded, studied, and reported on and I am finding my current model to be too rigid....we don't run the same parts or assemble the same assemblies all the time and each of them (inclusively) have their own idiosyncratic characteristics that need to be recorded - outside of dimensions. There are process-related issues that come up.

I am trying to avoid having a combo box on an inspection data entry form that has hundreds of items in it - most of which would be irrelevant to the majority of inspections.

I am going to go back through my older posts because when the EAV model was first mentioned to me, I believe that someone described it much like what a db would look like in a hospital emergency room for intake where you never know what you're going to face.... more rows - fewer columns (still wrestling with that).

I know you have beat this horse to death with me before - but I am hoping that we could revisit the conversation now that I have personally struggled with it on the plant floor and have a much clearer perspective. Diagrams are always appreciated.

Thank you as always,

Tim

 

[The_Doc_Man]

Combo boxes need not be so heavily overloaded if you can put some smarts in the design of the form to first determine the topic in a broad-brush way of thinking, then use the method of "Cascading Combo Boxes" to adjust the .RowSource properties of combo boxes after the one that sets the tone for the rest of the data input. That quoted string is what you would look up in this forum to see how that idea works.

In operation, this would have the effect of reducing the size of the list presented by each combo box you visit in turn. Like, if your first combo box says "pistol" then your next combo box might offer "bullets" and "firing pin" and "ammo clip" - but if you picked "sword" then the next combo box might offer "sharpening stone" and "replacement pommel" and "handle material." The point being that each combo in a cascaded series might refine the choices to be made in the next combo.

It can be a pain to set up the cascade logic because you might need a bunch of, essentially, junction tables to identify what would be in the next step of the cascade. But to add things? They are just tables, thus no need to worry about the logic of the form.

 

[Zydeceltico]

Combo boxes need not be so heavily overloaded if you can put some smarts in the design of the form to first determine the topic in a broad-brush way of thinking, then use the method of "Cascading Combo Boxes" to adjust the .RowSource properties of combo boxes after the one that sets the tone for the rest of the data input. That quoted string is what you would look up in this forum to see how that idea works.

In operation, this would have the effect of reducing the size of the list presented by each combo box you visit in turn. Like, if your first combo box says "pistol" then your next combo box might offer "bullets" and "firing pin" and "ammo clip" - but if you picked "sword" then the next combo box might offer "sharpening stone" and "replacement pommel" and "handle material." The point being that each combo in a cascaded series might refine the choices to be made in the next combo.

It can be a pain to set up the cascade logic because you might need a bunch of, essentially, junction tables to identify what would be in the next step of the cascade. But to add things? They are just tables, thus no need to worry about the logic of the form.

Thanks. I'm glad you described the above the way that you did. I hadn't thought about utllizing cascading combo boxes - and very intentionally designed tables and junction tables - as a way to "operationalize" what our plant process is in reality - OR - as a window to consider table design due to real life processes but at least in my case that could make a heckuva lot of sense. I can immediately see how I could robustly control process flow due to real life circumstances and retain much more than a semblance of normalization.

I tend to be a "visual" person so - if this makes any sense at all - junction tables tend to help me wrap my brain around more aggregated concepts.

I'll give it a go and post back.

Thanks!

Tim

 

[The_Doc_Man]

Glad I could help, even if only to give you nudge in a good direction.

 

[Pat Hartman]

Tim,
Didn't you have another older question regarding the inspections? Didn't I create an EAV sample for that for you?

 

[Zydeceltico]

Tim,
Didn't you have another older question regarding the inspections? Didn't I create an EAV sample for that for you?

I did and you did.

And finally - after working my way through many other aspects of the process I have a much, much greater understanding of what you were trying to show me and also how the model you provided is intended to function.

I am actually using your model to build the "bones" of the inspection aspect of the db. It makes sense to me now. Between then and now, I've developed a smaller db for a different aspect of work and unwittingly usd that exact model for it because I couldn't figure out a different way. Then the light bulbs went off - finally - and I dug up the model you gave me to really put the shine on the bigger inspection db. Still developing it but all is going smoothly and it will very much work with our robust RL work processes and needs.

 

[Zydeceltico]

Tim,
Didn't you have another older question regarding the inspections? Didn't I create an EAV sample for that for you?

Hi Pat (et al!)

So I have a question..... I am thinking about forms and how to operationalize forms from an EAV model given the EAV model and table design................

All of our parts and assemblies each have inspection items of a general nature (e.g., length, width, coating quality, etc.). It's a closed and relatively straightforward - pass or fail on a finite amount of general measures.

But the real value of this DB effort is in recording the anomalies - the things that DON'T exist all the time.

This makes me think about the medical example you shared with me a long while back. A patient goes in for an exam. The physician has no idea what needs to be measured. And in reality there could be an infinite number of required tests and measurements none of which will be predictable necessarily from the last patient the physician saw. It's really easy to measure "healthy" people but there is no value to the sick in measuring healthy people after a baseline for health is established.

I am putting my FE on a tablet so I can walk around the plant and record literally everything (especially the anomalies) - or at least that is the idea. So forms that are "written in stone" based on "healthy" products are not always going to work.

And just like the physician/patient...I want to be able to record maybe one entry and maybe 4 or 5 all related to the same InspectionEvent - but I won't know how many pieces of data I am going to collect until I am actually faced with a condition in the shop.

So......if a physician was going to record an exam of a patient when the number of pieces of data that might need to be recorded is unknown on the front end, what is an example of what the physicians forms might look like and how they function? If I could "see" an actual example I can probably wrap my head around how to proceed.

Doc mentioned the benefit of using cascading cbos and I get this but I can also see how I might have cbos branching in many different directions.

I know you don't have an "answer" for me but I always benefit from your insights. The medical model is pretty analogous to what I'm trying to do.

Thanks a bunch as always,

Tim

 

[jdraw]

Tim,

Regarding the Dr patient analogy, here is a data model (Barry Williams' site) that may offer some insight.

As for your own application, can you mock up some scenarios and test them against your model.
Good luck.

 

[theDBguy]

...but I won't know how many pieces of data I am going to collect until I am actually faced with a condition in the shop.

Hi Tim. I was afraid to respond since I know Pat will probably be able to explain it better, but I just wanted to say you're probably over thinking this a bit. About not knowing how much data is needed for collection beforehand, this is why we suggested the EAV model. Remember, we said each entity could have varying degrees of properties/attributes, so you simply store them as attribute/value pair rather than designing each attribute as columns/fields, which would require knowing those attributes beforehand. Not exactly the same but think of an purchasing order form where the main form is the purchase order, which tells you about the client and the date of the transaction. Then, the subform can contain from on to many "products" the client ordered or purchased. One client might have one or two items, while another client could have tens or hundreds of items in the subform. I think this is the same with your situation. One inspection could have one or two items found/tested, while another could have much more. The only difference is the rows in your EAV model will include the attribute to identify what each row signifies.

 

[Galaxiom]

Have a look at the sample database in post #3 of this thread.

It shows how to make a subform show virtual records from a list of potential records in a table. They only actually exist in the main table once a value is added to them.

Many developers use lots of code to write records to a temporary table to achieve the same as this does with hardly any code and no temporary table. The key to it is the outer join in the RecordSource query.

It is ideal for EAV use. I have not included the functionality but you can use a junction table to select which virtual records are loaded for a particular master record. I have only included a simple checkbox selector but the concept would be similar.

BTW I would advise you to be very careful allowing just any user to easily enter new attributes. Such databases are notorious for ending up with vast numbers of misspelled variants.

Put up a lot of hurdles that push them toward what is already in the list. Applying a Damerau-Levenshtein comparison to find similar terms can be a good idea.

See this thread for a DL function that works well.

 

[Zydeceltico]

Have a look at the sample database in post #3 of this thread.

BTW I would advise you to be very careful allowing just any user to easily enter new attributes. Such databases are notorious for ending up with vast numbers of misspelled variants.

Put up a lot of hurdles that push them toward what is already in the list. Applying a Damerau-Levenshtein comparison to find similar terms can be a good idea.

See this thread for a DL function that works well.

That is precisely what I am trying to avoid. While there may be none, one, or many conditions to record, they are very typically not complete surprises - just not consistent - which is why I want to record them in the first place and then review them later across a variety of stable, predictable fields to try to find answers.....so yeah........I'm trying to define "controlled chaos" that doesn't lend itself to allowing the user (which is only 3 of us) to make spelling errors. I know it has been really telling beta-testing this with a co-worker just how easily "off-track" things can go.

So pardon the pun - but "Form" definitely begins to hold as much weight as "Function" at a point.

Thank you for the explanation and pointing me to the "Grades" post.

Tim

 

[Zydeceltico]

Have a look at the sample database in post #3 of this thread.

See this thread for a DL function that works well.

...and thank you for this. I can see the value of that code tool....and also not having to try to figure it out on my own.

Much Thanks!

Tim

 

[Zydeceltico]

Not exactly the same but think of an purchasing order form where the main form is the purchase order, which tells you about the client and the date of the transaction. Then, the subform can contain from on to many "products" the client ordered or purchased. One client might have one or two items, while another client could have tens or hundreds of items in the subform. I think this is the same with your situation. One inspection could have one or two items found/tested, while another could have much more. The only difference is the rows in your EAV model will include the attribute to identify what each row signifies.

Hi DB,

So - very basically - when designing the form to capture the data for a new purchase order you describe above, what could you see as an accommodating design?

Are you able to point me to an example of a form meant for CREATING rather than reviewing a Purchase Order? One that - as you point out - may have one line item or might have 100?

The general info portion I have down. My frmInspectionEvent is exactly the "general" portion of a Purchase Order (Date, Time, JobNumber, Inspector, Operator, etc.).

I just don't have a "vision" of what the area for entering line items might look like. I am sure there are various means of skinning the cat.

I have this weird visual/spacial reasoning issue that is tough to explain but when I look at well-designed dbs I tend to grasp the underlying structure of the overall db design from looking at how the data entry forms are designed and function. For instance, I start entering data and then look at the underlying tables to see how they have changed and then look for relationships and at rowsources to see how the data is "moving around" under the surface of the forms and amongst tables.

Like I said, I'm a weirdo.

And I tend to design and create my own db in the reverse order. It helps my table and relationship design to have some working concept of what the forms are going to look like for the user to collect data.

It may be the case that I am trying to do too much with the form. I may be - in my mind - trying to create a report (finished purchase order) and a data entry form at the same time when what I should be doing is recording a single line item related to a particular event (PO) that later I aggregate into a report.

To continue with the purchase order example: I think I may be trying "to fill out" a Purchase Order as if it were to be a handwritten piece of paper in front of me where I can see ALL of the line items at the same time when that may not be the best approach.

 

[theDBguy]

Hi DB,

So - very basically - when designing the form to capture the data for a new purchase order you describe above, what could you see as an accommodating design?

Are you able to point me to an example of a form meant for CREATING rather than reviewing a Purchase Order? One that - as you point out - may have one line item or might have 100?

Hi. Any form/subform setup would apply here, I think. For example, if the first client bought/ordered apples and oranges, and the second client ordered bananas, you could think of it as the first product having measured on its height and width, while the second product was measured on its weight. Again, the analogy goes like this, where the purchase order details might have the columns like "product" and "qty," yours will have "attribute" and "value" instead.

 

[Zydeceltico]

Have a look at the sample database in post #3 of this thread.

It shows how to make a subform show virtual records from a list of potential records in a table. They only actually exist in the main table once a value is added to them.

Many developers use lots of code to write records to a temporary table to achieve the same as this does with hardly any code and no temporary table. The key to it is the outer join in the RecordSource query.

I looked at the Grades DB. Assuming that form EnterGrades is the main form, I'm getting an error message when entering data and I don't see where to actually enter grades.

However - - - - I looked at the underlying query and the index setup in table Grades and I get that part. My Parts, Products, and Assemblies tables are setup exactly like that - where there is a multi-field key where the combination of multiple field values defines an item. I use it kind of the same way in that each individual instance of index Field1, Field2, and Field3 in grouping table Assemblies have specific criteria associated with that combination and that combination only - even though there may be some overlap with other instances of the index. And surprisingly to me suddenly - I designed it on my own because I couldn't figure out a different way to do it.

And it was a lot of work but I understand it and it functions very, very well for my needs. So if that's the case, what I probably need to chew on and swallow is that - as some of you have clearly told me in the past - setting up a table structure for an EAV model for what I need is going to be a lot of work but will be really flexible and useful when I'm done. Currently I tried having the balance of my inspection tables be more of a traditional model. But like I said in an earlier post, I have found through beta-testing that I am recording primarily general measures rather than capturing the anomalies that are really the goal of examining against more baseline data.

...and this whole time I am reassessing how I perceive our real life business process. This exercise is truly forcing me to be objective about my perceptions of what we are trying to accomplish and how best to go about capturing that information....and being forced to be honest forces me to digest that it is not as simple as I would like it to be in some ways and much simpler than I expected in others.