Table of attributes and grouping of attributes thereof (1 Viewer)

After some experimenting around, I am starting to think that creating tables to manage attributes and linking them with categories is either 1) doable in implementation but almost impossible to present or 2) presentable but impossible to implement.

The big objective was to make the database self-maintaining even though if the metadata could change so there would be no need for a database programmer to come in and fix it up every time we enter a new fiscal year and have different reporting requirement than before.

Now I'm kicking around a new idea. What if I were to compartmentize the back-end for each respective category of what I want to store. There'd be a "central" back-end that holds all personal information of our clients for any services for any contracts and a front end that acts as a container for each back-end (e.g. copies the forms/reports and links the specific data back to the database).

Whenever a specific person falls under a contract or whatever, the users would select the subform representing the contract, which would add the primary key from contact database to the specific back-end and because the specific back-end can have a 1-1 correlation to the model it represents without any abstraction, I guess the development will be easier.

The only problem with it is it's still a multi-database version of "three-legged" tables which as The_Doc_Man demonstrated, is actually denormalization of data. At this point, I'm more interested in providing a database that is easy to implement and easy to maintain even if I've since left the company which was why I mucked with the idea of abstracting away the incompatible structures of different contracts into generic tables. Seems to me the idea of "pluggable database" is the closest I can get to keep things general without getting bogged down in exotic implementation or bizarre table layouts.

Anybody have a suggestion or leads for me to research?

Any suggestions?

Invariably, there is a hybrid way that will let you implement things that don't seem possible - but the implementation will have to "fudge" around a little bit. You simply have to find a happy medium. (Give a fortune-teller a big gift? But I digress...)

When your head is spinning because you cannot tie everything together it is time to step back and ask yourself... why do I have this problem? (Other than this silly desire for a regular paycheck, of course.) What is the barrier to implementation?

If your problem is that your tables seem too deep and have too many relationships, is that because of your problem or because your solution went that way? One of the themes that you keep returning to is that everything changes for each contract, i.e. you have so many options that are contract-specific or employer-specific that you go nuts. But the question I have is, how often do you need to know the restrictions?

Lemme 'splain that. It might be a way to do a classic "Julius Caesar" approach - divide and conquer.

You are setting up these attributes for a given situation and you are making all sorts of tables to identify who has the right to do what, and then your relationships go all dingey on you because they are convoluted. But once the tables of attributes are set up, do you really need to know any more WHY attribute X is present but attribute Y is not? I.e. building the junction table of attributes is complicated, but once it is built, do you need the relationships that built it to still hang around? I know I'm speaking in general terms but I'm just tossing ideas loosely into space here anyway.

If you can divide up your relationships into "needed during definition phase" vs. "needed during execution phase" vs. other phases, you can simplify what you have to see at what time. Which might be useful in simplifying your life here. Is this concept even relevant in your case?

I will think about this carefully.

You are correct that I am returning to same theme because I'm in a sludge right now and am not sure what I need to do to get out of there, short of yelping for help.

The reason why I get confused is because I keep thinking that if I were to make a database for one contract, I'd have X one-many relationships among attributes, while another would have Y one-many relationships among attributes with a slightly different table structure overall.

Now trying to abstract this invariably requires that I flatten them into table and model the relationship using tables, which is where I get all confused. See, if I wanted one attribute to be "child" to another attribute, I'd need to use a group/parent table or similar model like I described in my post three post above. Then I'd need to add an "instance ID" for each time I need to add a new child to keep track of what is what for a given entity.

That's why I thought what if I took a step back and do it from pluggable database approach so I can keep one-to-one correlation between the table structure for each back end and the real world model it tries to represnts. Try to put it in a table or set of tables, and I get totally confused about how can I model it and feel as if I'm reinventing the database software.

Did that explain the problem?

Attributes can have child attributes? Yes, that is nasty. But let me ask if they have to really be parent/child or if - as attributes - they can be siblings. In an infinitely object oriented world, what you want would exist and Access might even handle it - though we must not forget that Access is not truly object-oriented. Just "mostly" object oriented.

I can see a person who fills a role as having attributes. Happens all the time in ORACLE. Fer instance, (no pun intended...) the attribute of being able to start a session via SQLnet. The attribute of being able to grant access to an ojbect. The attribute of being able to update an object. These are like permissions - which are certainly one type of attribute. But what I don't understand is how attributes can have attributes. Is this the back-door to tell you how or why an attribute exists? What bugs me is that in my previous discussions with you on this problem, PEOPLE have attributes - which they got from the ROLE having attributes. I.e. a type of back-door inheritance.

But where I keep on trying to delve - and get nowhere doing it - is that your attributes are so general that I wonder if they have abstracted themselves to no longer have any relevance to the original problem. I get that way sometimes in some of my system utility programs. Go out swingin' the old machete as I hack my way through the bushes - only to realize I've lost sight of my goal. Your own doubts are what makes me wonder if this has happened to you?

I think this illustrates the dangers of trying to create a general purpose solution in Access. The level of complexity needed to solve the the general case is nearly always greater than that needed for a specific case. It is always easier to have an exact business model than trying to envisage the general case.

In an ideal world there would be a lot to be said for solving the general case but in the real world a specific solution is always going to be as efficient and usually much more efficient.

Just my two cents

Rob

Rob, I'm with you on this one. Your two cents were golden pennies, I assure you.

On a different direction, I'll share something I learned a long time ago during my dissertation research.

If you give a computer data, it'll crank that data just fine. BUT it will never know or care whether that was the appropriate action to take on that data. See, it trusts YOU to make the hard decisions.

In my case, the question was linear regression to determine slope and intercept values. This is so old a problem that it was done by hand before the advent of computers. But where you run into a problem is this: Yes, you'll get a straight-lline equation out of the math. But should have even tried the LR in the first place? If it was really QUADRATIC data, no you should not have. Or cubic, quartic, or higher still. Or sinusoidal, chaotic, take your favorite flavor of the month. That's why you include data quality measurements to validate the applicability of your process. If the correlation coefficient is closer to 0 than to 1 in magnitude, or if the standard error of the estimate is greater than your intercept value, you've got questionable data. When I did the work on my dissertation, most of my data was in the 95%+ correlation coefficient. Some as high as 98% and even an isolated 99% for some of the slower reaction rates.

Well, your attribute issues can have the same problem. Sure, you can abstract them down to a fare-thee-well. But should you? Do you lose relevance when you do? And another question: At that level of abstraction, did you over-normalize?

Yes, over-normalization can be done. My previous questions about using the timing of something's applicability to decide when a relationship needs to exist and be used. If you leave that relationship in place after you no longer need it, you might have ended up over-normalizing something. I repeat my question in that light: Once you have defined the attributes in your setup phase, they are defined. Do you need to make the other parts of your application track all of the details of how those attributes got that way? Or can they just track the now-defined attributes and be done with it?

Rabbie,

I'm starting to agree with you. I hoped that if I could create a general solution database, I would be doing my organization a huge favor by making it much easier to maintain whether the rules of game changes (which they do at least on an annual basis) wihtout having to ensure that there's a DBA around (they don't have one; I'm an impromtu and defacto DBA, and believe me, this is in category of "other duties as applied" when looking at my job description.)

But the more I think about it, I realized that even if I did succeed, I'd still have to write out new reports for new contracts, and I can envisage that if I left, I'd pity the poor sap who had to figure how to organize everything so it can be reported properly. Since report is pretty much required, building a specific form is a half step away, really.

The_Doc_Man, I am sorry if I've not been clear with the problem of "attributes having attributes". It's not that I think attributes needs to have attributes but rather grouping attributes together and making another group of attributes a child of the first group. In other wise, I want to represent one-many relationship using table of attributes and group because I do need to know which instance of attributes applies to what instance. In practical terms, if I define a case as an attribute, that's quite fine as this is a one-to-one to the client (well, I can have several cases as I like but I disgress). But the case itself needs some specific information for *every* visit, so that's another one-many relationship, and if the client has two cases going on at same time, i need to be tell which instance of attribute belong ot which case. This is the quagmire I'm finding myself because as I said above, it's either a PITA to implement or PITA in upkeeping.

But I'm leery of using the pluggable database because that is basically going back to the three legged tables we analyzed earlier and shown to be denormalized.

Did that help the matter any?

I know your nomeclature probably comes from your business environment, but attributes in this case don't convey anything to me.

You have a client on a case. Your client visites a case worker. The case worker handles many cases, many clients. A client can have many cases if they are based on different situations. The word "attribute" has baggage that doesn't belong in any part of this problem. Don't think I'm being unnecessarily harsh here, but you are borrowing trouble by using words with baggage to describe something that doesn't fit in with that baggage. As far as whether your contract requires it, SCREW them. Think out of the box. Use a word that MEANS something for problem description.

Sorry, that had to get out in order to clear the air.

OK, back to reality. First, anything worth doing with any complexity to it is DEFINITELY going to be a PITA. So no "DUH" there. Second, I keep on coming back to this concept of attributes. Which, if I recall correctly from earlier discussions in other threads, could be events along the progress of a case time-line. You are continually impaling yourself on defining the attributes this way or that. DON'T DO IT. Try thinking along other lines because it is clear you are mired up to your quag. (Quag mire .... get it...? Never mind, not that funny anyway.)

Suppose you had a TEMPLATE of possible events for each type of case. Then each case has a template number as a reference to what can be part of it. Now your template could just be a junction table between the template number and a table of ALL possible events or attributes without regard to who can have them or why. ONE TABLE. The template that tells you what can happen under contract X will be different than the one for contract Y etc.

When one of these events occurs, you have the master index of that table of possible events. It won't matter HOW many templates have the event "Client died of old age waiting for the appointment." Only one number applies to the event. But you can get to that event from as many templates as have a junction table entry to it. Your earlier discussion was an attempt to find heavy-duty ways to define these tables of attributes automagically. But there is only one table of these events or attributes - and one more junction table to define legality of each event in a particular context.

Then you define that one table ONE time for each possible template number. Then you assign template numbers to cases and you are done. More or less...

OK, I'm over-simplifying. But worrying too much about the reason attribute 31 is in the list will only give you grey hair overnight. If you can do X, Y, and Z under contract A, then all you need to know is to reference the template number that links you to the list of valid events or attributes. Once that list is built, you do not CARE why it is like it is.

The mental spiral is caused by chasing your tail too much. Trust me, my friend. It is time to back off and examine your definitions for what you need for the problem vs. what you would need for the more general solution.

I think I have the gist of what you are trying to saying, The_Doc_Man. But let's go and start from the top. I'll try and apply your propose as we progress to see if we got it.

The ultimate purpose of the database is not to simply keep data of any casework done by my users, but also to ensure that they are satisfying *all* requirements of various contracts that a case may fall under. Therefore, data integrity is essential, and the database must be strict in the sense that it will require so and so questions to be answered (as a group... or more like a transaction. We can only have a event if we have all the information for the event or it's a nonevent). Therefore, my users does not want to worry about details and what they need to fill in; they want to be told by the database what is needed for a particular event and they will fill it in.

A side objective is to see if I can truly get the database to accept one data and automagically assign it to two or more contracts' requirement, therefore simpifying the data entry and clamping down on the data integrity.

Now, contracts themselves are various, and has different rules and requirement of what information they want us to report. Furthermore the contract may change between fiscal year so we need a easy way to update the new requirement without having to overhaul the whole database (which is my mistake with the existing database; it's now obsolete by the new contract).

Not all services in contracts are case management, but let's focus on them for the remainder because they do constitutes the bulk and most complex operations of the database.

I would start with a small set that is always true for any case. That would be the date of visit, name (actually, ID) of the client and the caseworker and maybe a general progress notes (I say maybe because not all notes should be reported to all contracts- will get back to that). I then add a table that lists all possible events; a new case has been opened, a progress has been made, a new objective is stated, etc etc. Then I join it with a contract to list what *must* be answered for that event. In Case Open, I would tell the database to automagically accept the visit date (it's already there). In a progress, it would again accept the visit date but ask users to select from a list of topics for the contract to report and so on. No need for parents or grouping of attributes so far.

But, opening a new case requires a new topic which is under the progress event, so I'd need to somehow be able to tell database that "if this happen, THAT must also happen" so my users can be truly relieved of cumbersome task of remembering to fill in all the required information. While I can certainly do that in VBA, that basically defeats the premise of the system; that it would automagically know what is required based on entries in a table or a set of tables without hard-coding any specifics at all. This is why I'm chasing my tails over the parenting and grouping of the attributes because I want to be able to dictate what sequence of events can happen.

As for the one-data-entry-for-multiple-contract, this further obsfucates the complexity in managing attributes. I think you tried to tell me that I should just have one table of attributes, that is basically freestanding and let my users input the relevent data for attributes and let database take care of what attributes goes to which contract (and apply it to multiple contract if desired and only if the attributes are 100% identical). The trouble is, it's not always atomic. Consider the Topic section of one contract. There are five things I need to know; Topic (selected from a list), Date it was identified, Date it was resolved, Issues Resovled XOR Barriers Removed. In my earlier analysis, I thought it was logic to "group" them together so I can ensure that all data will be filled in for this "group", and can display to my user when they review the case because the group can't be completed in one day and they will want a reminder that this is still pending completion.

The idea of having a template for my users to fill in would make everything simple, except for the ugly fact that some events can be repeated along the case's lifetime, which is why I need to somehow emulate one-many relationship.

Wait. If I made a template that simply told my users what events were allowed, selectable from a list. In a subform, they add a new record of a event, which would allow multiple instances of same event for same case. Because they already selected a template (e.g. contract), the database will automagically know what data is required to complete a event and display the appropriate fields in return... Is that what you were proposing?

But what about the one-data-entry-for-multiple-contract? If I select from a template, I'd need to help the database to somehow parse whether we want so and so data to be copied from one template to another template?

Okay, I'll have to mull on that. Thanks for the hand, Help. (John Bunyan's Pilgrim's Progress)

It is not easy because you really DO have a nasty problem. But I think you are getting to the point where you have some ideas here about why you are having the headaches.

This is one of those situations where you have to decide whether what I'm about to name is denormalization, obfuscation, or general hard-headedness.

Let's say you have Event X as a possibility and your template says "OK, this is what you use for event X regardless of which template it is in." But then you come across a template where event X can be repeated. How do you handle that?

Well, I can think of two ways.

1. Have event X and event X-repeatable as two separate master events, then choose the correct one in the template table that is the junction of template number and events.

2. In the junction table that shows what events are part of a template, have a couple of fields in the JUNCTION TABLE that show whether (and how often) repetition is allowed. Since the JUNCTION TABLE entry is specific to the template number AND to the event number, you can have unique data in the junction table entry. Data that would change from one contract to another can be recorded easily enough because those contracts would just refer to different templates.

Now, the trick would become "template building." There, you can have relationships that apply ONLY DURING TEMPLATE CREATION. Once the template is created, it is there and you really couldn't care less about why or how. The "main" portion (compliance portion?) of this beast only needs to look at the templates to see if events exist corresponding to the entries in the junction table that defines the templates.

Backing up a level, once you have a case and client, you should be able to assign the proper template in some record - perhaps the individual case record but perhaps not. Then when you go to enter events that relate to the case, you can filter against the case's template first to see if that event is legit. If it is, you record the event in some sort of events table that includes the event number. (Immaterial at this point whether it also holds the template number since you already validated it against the template, right?) At some point you can decide by comparing the template to the events list whether you have compliance or not.

Now we can talk about a separate compliance table. Let's say that event X is not valid until event Y is also recorded on the same or later date. You can build a COMPLIANCE table that includes template number, event number, and dependent event number. Then you can say that for template 123, Event X (initial application) must be followed by event Y (review of application) and event Z (manager approval of application.) So you would have two entries - <123, X, Y> and <123, X, Z> to describe proper sequencing of events. Complicate it if you wish by adding time-order constraints such that Y must precede Z. This is similar to the concept of a self-referential table where two of the fields refer to different entries of the same table.

OK, logic issues of using VBA. (You kind of mentioned that in passing.) It might well be possible to totally avoid use of VBA, but why? Oh, I'm not talking about using VBA to act as a filter - though that is possible. You could probably do some sort of query to validate things against that template table. But VBA can ALSO be used to do query sequencing for you. I.e. run an update query, then run a report, then do an append to some "exceptions" table, etc. etc. etc.

This use of VBA isn't necessarily a cop-out. Use the tools that are available and don't be afraid of them. Obviously, keep in mind the possibility of upgrading to something other than Access if needed, but all that means is DOCUMENT what you do.

Again, just tossing out ideas. Maybe I'm leading you astray. (Us old N'Awlins boys do that a lot, you know...) Think on it some more.

I'm trying to see how I would implement this. Let's see if I have the structure right:


Contracts: Lists all contract we held, are holding and may hold in future. Stores the name and effective term of the contract.

Events: Lists all possible events that can happen along the case's life, regardless of whether a contract want to record it or not.

Attributes: Lists of all required data for a given event. Related as a many side on one-many to Event table.

Compliance: Lists the business rules for each contract such as what order a event may come in, and whether another event is required concurrently with an event, and so on.

Persons: Lists all person, whatever their relationship to us may be. Their personal information are either stored in this table or the many side tables related to this table.

Visits: Records the visit by a person, with the pertinent data. Is a many side to Person table.


Templates: Links all allowable events with a given contract. Add some more fields to identify whether a event is repeatable, required, and so on.

Case: Indicates whether a Person has a case (e.g. a template number) with a certain contract. When the person is added to a new case, users is given option to multi-select the templates.

VisitEvents: A many side table to Visit table, links a visit to several events and records the actual data for a given data.


1. When a new person is added to the database (or maybe a existing Person record is elected) for a new case, the user is presented a list of all available contracts that can be multi-selected. This will update the Case table.

2. When user access the Visit form, the database will check what templates are allowed and gather a list of all valid events for all templates THEN look at the VisitEvent to see what event already are there and check compliance table to filter to final set which is now selectable from a combobox.

3. User selects the event from a combobox, which modify the subform of VisitEvent to show all required attributes for a given event for the user to fill in. User can repeat the process ad infinitum.


This is now more complex because we lost one-to-one correlation between data entry and reporting. For most times, the reports will be simply exported to a pre-defined forms (in .doc or .xls) provided by the folks handing out the contracts. Therefore, I *think* I need another table to define how events are to be reported as a group.

In practical terms, let's consider a section of a form that requires us to report what topics we talked about for a case and the date it was identified then resolved. In event table, I'd probably have this as two separate event (using more general terms... like say.. Goal Identification and Goal Met). The database would need to parse that those two events fall into one section for the reporting.

I could alternatively just create event one-to-one reporting if I could somehow display them in a continuous subform so the users can see what blank fields needs to be filled in. Unfortunately, Access doesn't like have dynamic recordsource between record for a continuous forms neither will it brook a subform within continuous form.

Am I on the right track now or am I getting nowhere now?


As for VBA, I wasn't clear. I don't intend to avoid it entirely. I, however, do intend to avoid using VBA to address *specifics* about a contract. As long it's general and abstracted, like what I described in Automagic section, no problem at all because it *always* works for any template, events, contracts, whatever. But once I use VBA as a shortcut to address a specific instance of contract requirement, IMO, the whole point of abstraction is out of the window. You either do it 100% or 0%, nowhere in middle.

Which brings me to next point. I'm sure I can compile a list of all possible events and figure out what specific requirement of a given contract meets what event. But suppose this contract had baggage associated to that event that isn't usually addressed so I need to do an exception. Would I handle this in Compliance table and pointing to a special event or maybe have another table for Exceptions to ensure that I've satisfied an oddball's insatiable urge to demand exotic data for God-know-what purpose?

Well, Jeez, Banana - I've got to leave SOMETHING for you to do...

At some point you will have to decide how you want to handle it. The perfectly general case, which is drawing you like a moth to a flame, or allow some customization to creep in to what you are doing. It's all a balancing act. At some point you have to stop designing and start implementing. Overthink often means underact. (And yes, it is often the case in theatre that overacting means underthinking! So there is a real-world corrolary somewhere out there.)

As to losing your reporting correlation, ??? didja ever really have it with all those rules and exceptions anyway?

Sorry, didn't mean to come off as a leech. I find that I think better when I write it out or else I get too confused.

I take that I finally got the ideas you were trying to convey, and that's good. At this point, I'm going to see what my uppers think of the proposals now that I actually have something more cohorent and work from there.

Why don't you send me your address so I can mail you a turkey for Thanksgiving? (Seriously, I really, really am in gratitude of your insightful assistance for the last month or so!)

Thanks, but no gifts accepted. I do this because I want to help folks. Your problem merited special attention because I saw something in it that was above the typical "Won't Someone Else Do My Work For Me" type of post that so often plagues these forums. And you kept up your end of the bargain by doing some serious reading and analysis. This makes it a good, healthy exchange of ideas as opposed to spoon-feeding.

No, you aren't a leech. You have a really difficult problem that leads you to analyze to a fare-thee-well. Sometimes though, I find that at least trying to implement my problem after a lengthy analysis suddenly clarifies what I was trying to do. It makes it possible, nay, likely that DOING begets more UNDERSTANDING than any discussion ever could.