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

[Banana]

My database uses a table of attributes to define fields that could potentially change or be added, depending on what contracts we have open for our fiscal years.

For starter, I'll describe the structure I would normally have done if this was a database specially for a contract.

A contract includes several services.

A services may have roles, usually one, but is one-many because I cannot guarantee that it always will be one-one.

One of role being defined for a current contract is "Case Management Client".

We are required to ask our clients what their needs are for communication and what reasonable accommodation we can provide to them. This is "Check where all apply", so it's a many-many relationship with a lookup table. All other personal data already have their respective table to go to.

Next, a client can have cases. Most of time, it'll be one per contract year, but there is possiblity that it could be closed mid-year, then re-opened the same year. Therefore, one-many client->cases. We also need to know when the case was opened and closed.

Now, every case has topic that needs to be resolved one way or other. We then need to set date topic has been identified, when it's been resolved and how it was resolved. So, one case -> many topics.

To summarize:

1 Case Management client -> Many Cases
1 Case -> Many Topics

Now, because I have no guarantee that contract terms will be same whenever we renew, and because we need to allow incorporating in new contracts, I need the database to be flexible so it's a simple matter of adding the required information then everything takes care of itself and I just need to worry about the reporting. This is where the table of Attributes comes in the picture.

tblAttributes
AttributesID (Primary Key)
DataTypeID (Lookup table specifying what kind of data type this will use)
ControlTypeID (Lookup table telling what control this uses)
FieldCaption
RequiredEntry
ValidateOnEntry
ValidateRule
ValidateText
AttrDefaultValue
LookUpTable (Identifies if an attribute uses a lookup table; all lookup tables are many-many by default)

This is basically a copy of what you would see on table design view. The attributes applies to both services and roles.

For example, we need to ask everyone what their communication needs and reasonable accommodations are; this would go under Role-Attribute, as this is something descriptive of the role as a client, and is asked once.

Then there's service-attributes; that's where we put the case and topics in.

But here's two problem: As I described, a case can have many topics and the tblAttributes "flatten" the one-many relationship. I'd need a mean to somehow represent the relationship between the attribute.

Secondly, I'd need to be able to "group" the attributes as well. For example, a case has a "Case Open date", and "Case Close date"; which are currently stored as two separate attributes when in the above design, would have been in one table.

My initial ideas are:

1) Add a field and name it "AttrTag", something akin to Tag property used in control objects; to tell me something about how attributes will be group so it can be grouped in a meaningful way.

2) While the #1 is a good solution for the second problem, it won't be as good for the first problem, as if I need to do a query of virtual one-many attributes, this is strictly one level deep; what if I need it to be two level deep? I'm SOL if this happens. I thought about self-join, but wasn't sure how this would help un-flatten the implicit relationships between attributes.

Any suggestions will be much, much appreciated!

 

[The_Doc_Man]

Let's do a little bit of Julius Caesar on this one - divide and conquer. (And keep an eye out for Marc Antony.)

For the problems associated with a contract closing and re-opening because of a fiscal year boundary, you can make a PROCEDURAL solution rather than a data solution. This is an excuse / requirement for declaring a new role that can be as different as required. Abandon the old role entirely rather than try to track new requirements that didn't come along until mid-way through the project. If you do that, the role's attributes no longer need to track dates. The role itself can do that.

I'll have to mull over the rest of that stuff.

 

[Banana]

Thanks for replying.

As matter of fact the Role table only has one checkbox, "Inactive", which will be checked whenever a contract expire via VBA, so already there.

I do, however, intend to re-link old attributes to new roles, if the new contract continue to use same attributes for a similar role.

 

[Banana]

Milling over it some more...

I wonder if I should add yet another table; call it AttributeGroup, which simply identify what attributes belong to what group and allow it to be self-referential, just like that another thread about recipes being used as an ingredient for a complex recipe.

This would, presumably, give me unlimited depth, and descriptive power needed to manage the attributes.

What do you think?

 

[The_Doc_Man]

Anything is possible given clever design and lots of elbow grease.

You have the choice of trying to deepen your attributes - meaning recursion will be required. OK in VBA - HELL in SQL.

Or you can broaden and flatten attributes. OK in SQL. Question is whether it permits you to accurately represents your data set.

Two questions therefore arise:

1. Would flattening invalide the descriptive power of the table?
2. Will SQL be required to manipulate this beastie?

I don't have an answer because I continue to fail to see the complexity you claim to be there. To me, attributes never require depth beyond one level. At most one could say that some common groups of attributes occur, but that is more of a problem of instantiating the members of the group for the given case. Once the attributes are linked up, how they got that way is of no consequence.

 

[Banana]

Well, the database is being built based on the assumption that SQL should come first, VBA only if needed. This will eventually be migrated to MySQL engine. Therefore, everything in database should be reasonably done with SQL whenever possible.

To answer your other question about whether descriptive power will be invalidated- I would say no, because I can re-link attributes for each new entries, so I can have as many attributes I will ever need.

The issue is making it easier to be logically grouped.

Maybe if I try again and describe what I'm seeing as "complexity I claim to be there"....

Let's assume we are making a database for *THIS* contract; it lives and dies with the contract. Therefore, we don't need a table of attributes because this is just for this contract. One part of contract is case management. Let's concentrate on this.

The structure would then be:

tblPerson
Personal Information; has several many side tables about other personal information as well.

tblService
ServiceID
Service --- This is where we'll have "Case Management" part of the contract in, as the contract has several services.

tblCases - A client usually will have just one case per contract year, but there's possiblity of a case being closed mid-year then re-opened in same fiscal year.
CaseID
PersonID
ServiceID -- Case Management Key
CaseOpen
CaseClose

tblTopics
TopicID
CaseID -- Foreign key to Case table
Topic
IdentifyDate
ResolutionDate

Basically,

One client -> Many Cases
One Cases -> Many Topics


With me so far?

Now going back to our original database, we cannot use tables as described above as they have no permanency; the contract will end next year, and terms could be changed, and what if we have new contract, etc. etc.

So that's where we have tblAttributes, tblRoles. We still will hve tblServices, since *all* contracts have different services within. Both roles and services will be linked to attributes to describe the attributes of each roles or services.

Now, here's my data entry (not data structure!) of tblAttributes-tblRoleAttributes query.

RoleID     AttributeID    AttributeName
1            6                 CommunicationPreference
1            7                 ReasonableAccommodation

RoleID=1 is the key for "Case Management client". Here, we describe something that is only true about the person occupying the role itself; in other words, questions that can be asked once.

Now for similar query but linking services instead of roles:

ServiceID  AttributeID  AttributeName
1             1               CaseOpen
1             2               CaseClose
1             3               Topic
1             4               IdentifyDate
1             5               ResolutionDate

tblRole and tblService can be joined together in a junction table, so this represents many-many relationship for all roles and all services. In THIS case, Role "Case Management Client" is one side to all service-attributes listed above.

The catch, however, is there could be many topics associated with ONE case; yet the case attributes are in the same table with the topics! Therefore, I need some way of representing the relationship amongst attributes.

I hope I've made this a bit clearer. That said, does the idea of creating a AttributeGroup table to emulate the relationship work well for SQL? I'm not sure what you meant by "broaden and flatten"...

 

[The_Doc_Man]

OK, let me take a shot.

tPersons - PK is PersID, you know how to normalize...

tContracts - PK is ContrID. This holds start and stop dates.

tServices - PK is ServID - THIS IS A DICTIONARY OF SERVICES, STAND-ALONE
It is not yet customized to a contract... this is what your company CAN do.

tContrServ - junction of tContracts and tServices - what the contract calls for. Assuming you can have multiple persons doing same service, give this table two more fields: PK is CntSrvID, extra field is NumInRole: number of persons required to provide this service

tIncumbents - junction between tContrServ and tPersons

tServAttr - PK is SrvAttID - THIS IS A DICTIONARY OF ATTRIBUTES, STAND-ALONE, showing what you can and can't do.

tCntSrvAtt - junction between tContrServ and tServAttr showing that this service for this contract has these attributes.

What you are calling services, I would call events... but hey, a rose by any other name still sticks the hell out of you with its thorns...

tEvent - PK is EvtID - THIS IS A DICTIONARY OF EVENTS YOU CAN PROVIDE OR MANGE

tCtrEvt - junction between contract and events in that contract, possibly adding reference to the person responsible for the event, possibly including a date and other info specific to the event.

 

[Banana]

I've mulled over your proposal, and I'm not sure how this will solve the problem of relating attributes to other....

I'd think that questions about case for a client (when did it open/close?) would be an event. Likewise, I'd think that whenever a client has a new topic or has resolved a pending topic, that would be an event...

IOW, case events and topics events still are in one table When all I need is a way to group them and give them resemblance of relationship... Or am I missing something obivous?

 

[The_Doc_Man]

case events and topics events still are in one table

There are several questions to consider.

1. Would the format of a case event and the format of a topic event be identically the same?

2. Do they mean the same thing down to the lowest level of data recorded.

3. If #2 is YES - then why do they have different names? If #2 is NO - then why are they in the same table?

You are looking for either a DATA reason - or a meta-data reason - to split these two items that you treat differently.

To my mind, if you have a definition of what events you support, then your case event table is just a junction that records a date/time, case #, and event # (i.e. very brief). Optionally but plausibly, add in employee ID to note "whodunit" and that should be all you need. If the event is duplicable, then a sequence number computed by the 1+DCount() method or 1+DMax() method so you can have events starting from #1.

I guess I'm asking what is the difference between a case event and a topic event. That has meaning to you but not to me. I INFER that one is prototypical and the other is actual, but you might have other intentions.

If I am right and one of those is prototypical, then it is an event that doesn't exist until you instantiate it in the other table. As such, it should NOT be in the same table.

You have already admitted to being confused. I am somewhat confused by your use of language with connotation (baggage) not visible to me. Therefore, it is possible that we are talking past each other.

But then, I work with the US Dept. of Defense, so I'm used to running into words with lots of baggage. And misunderstandings? Naw, NEVER happens within the gummint.

 

[Banana]

1. Would the format of a case event and the format of a topic event be identically the same?

Are you talking about the data type and how it is stored? If so, yes. Basically same thing.

2. Do they mean the same thing down to the lowest level of data recorded.

No.

3. If #2 is YES - then why do they have different names? If #2 is NO - then why are they in the same table?

You are looking for either a DATA reason - or a meta-data reason - to split these two items that you treat differently.

Precisely.

I guess I'm asking what is the difference between a case event and a topic event. That has meaning to you but not to me.

...

You have already admitted to being confused. I am somewhat confused by your use of language with connotation (baggage) not visible to me. Therefore, it is possible that we are talking past each other.

No trouble at all. The words I get from our government (we have contracts with them), and they like to manufacture up exotic ways of recording data. Hence, me pulling my hairs out here.

Maybe if I describe what our caseworker will do with a client.

We have a new client come in for a case management. Our employee then enters the client's personal information in the database. On that day, the client's case is opened. That's CaseOpen event.

On same day, client and employee discuss about what problem the client is having and develop a plan of action. This is a DateIdentified event for Topic.

They then schedule an appointment a month later to see how they've progressed in resolving this topic. The next appointment comes, and client describes another problem that prevent a resolution to first topic. The employee then add another DateIdentified (another Topic) for this case, and make a plan, schedule another appointment.

In third appointment, client return and both topic has been resolved. The employee record both thusly (two DateResolved event under Topic). The client then indicate that the client is satisified with the present situation and is done. Therefore, the case is closed (that's CaseClose event).

Two months later (still in same fiscal year), the client returns with a entirely new problem. The case is then reopened and topic identified. A plan of action is made, then the employee schedules an appointment. Only this time, the client doesn't show up, and doesn't return any calls and has missed out the subsequent, so in three months from second case opening, the employee then closes the case, even though the topic has been unresolved.

So in the database, it should show that the client had two Cases, Case A and Case B:

Case A had two topics, one identified on same date of Case Open, another identified later. Both were resolved on same day when Case A closed.

Case B had only one topic, but never was resolved due to absenteeism, and was thus closed unresolved.

Does this help differentiate the Case from Topics and explain why I need to somehow represent the relationship between them?

But then, I work with the US Dept. of Defense, so I'm used to running into words with lots of baggage. And misunderstandings? Naw, NEVER happens within the gummint.

Honest, guvernor, mah dun't know whatta u'see talkin' abut!

 

[Banana]

Perhaps anyone have a suggestion?

 

[Rabbie]

Just to clarify this in my mind are you saying that a Topic can only be associated with one case even if different cases can have similar topics. In that case there is a clear one to many relationship between Case and Topic. My apologies if I have misunderstood what you are saying.

 

[Banana]

This is 100% correct.

As I described in my earlier post (#6), if the database lived & died with this particular contract, I definitely would have made two table; one Case to many Topics.

But the database will hold other contracts that has its own services/attributes and therefore requires higher level of abstraction so employees can manage multiple contracts. This is why we're using a table of Attributes to give details for each services provided by each contracts. But the dilemma here is that attributes "flatten" the relationship between cases and topics so I need to come up with a good way to model attributes' relationship without actually creating the tables (because they would be dependent on contract, and whether it remains same the next fiscal year and the year after that; no permanency at all).

Does that clarify the problem I'm trying to solve?

 

[Rabbie]

I see now where you are coming from. However I feel a well designed solution can often be adapted to an apparently completely different problem. I do not think it possible to model future relationships in a meaningful way until you know what they will be.

Personally I tend to work on an evolutionary road, developing the solution to fit the problem and expanding it as necessary because too often in the past the assumptions made about future developments have been overtaken by reality.

 

[The_Doc_Man]

So a case worker gets a client because a client has a topic needing some level of discussion. As part of the process of resolving this topic, it is possible to unearth another topic. Nominally, the case worker is not finished with the client until all topics for that client have a Close event.

Under the terms of the contract whereby the case worker is employed, there are some rules of behavior that you see as attributes. Since a person can be assigned to work on multiple cases potentially under multiple contracts, you are trying to model this in a way that makes sense.

If the above is true, the attributes belong to the contract UNLESS the contract spells out different rules of behavior for different categories of "client topic" to be handled. If what I described above is even close to correct, then the attributes you have been describing go either with the contract itself or a join of the contract to a category of topics enabled by the contract.

And this is why you have so damned much trouble. Frickin' government contract wants to use words that have too much baggage. Next time tell 'em Doc said go blow those words out their arse and use plainer language that has fewer conflicting connotations. This is why it is so hard to understand.

Contract --> Categories of Topic --> Rules of behavior (Attributes)

Contract --> Categories of Topic --> Allowed events

Client --> Topics (links to Categories of Topic to derive rules & implied link to contract via back-link of Categories of Topic. Topic has ID number.) --> Events

Agent --> Topics via ID number (links to Client via ID number). Implied link back to contract through categories link.

Company --> Agents (if they can cross then via junction; otherwise parent/child)

Company --> Contracts via junction

Company --> Company via self-join if there is one prime and some number of sub contractor companies.

 

[Banana]

Oh boy. To be honest I'm not sure if my head has stopped spinning yet.

But I will definitely try!

So a case worker gets a client because a client has a topic needing some level of discussion. As part of the process of resolving this topic, it is possible to unearth another topic. Nominally, the case worker is not finished with the client until all topics for that client have a Close event.

Correct.

Under the terms of the contract whereby the case worker is employed, there are some rules of behavior that you see as attributes. Since a person can be assigned to work on multiple cases potentially under multiple contracts, you are trying to model this in a way that makes sense.

Precisely. Mind you, a contract can have several provisions requiring different behaviors. See below for more details.

If the above is true, the attributes belong to the contract UNLESS the contract spells out different rules of behavior for different categories of "client topic" to be handled. If what I described above is even close to correct, then the attributes you have been describing go either with the contract itself or a join of the contract to a category of topics enabled by the contract.

Junction table is used here as you described in that last clause.

To clarify, a contract can have multiple "topics" (in your parlance), which I have been calling "services". So,

Contract --> Categories of Topic --> Rules of behavior (Attributes)

Exactly how I have it set up, but with naming thus:

tblContract --> tblServices --> tblAttributes

Contract --> Categories of Topic --> Allowed events

Now, I don't have a table of events, so will try this route.

Client --> Topics (links to Categories of Topic to derive rules & implied link to contract via back-link of Categories of Topic. Topic has ID number.) --> Events

I already have a junction table, tblPersonRoleAttributes containing the fields:

PersonID (Foreign key to tblPerson)
RoleID (Foreign key to tblRoles)
AttributesID (Foreign key to tblAttributes)
Answer

I should that the tblRoles is a many side table to tblServices, so services are implicitly described in a given roles.

Agent --> Topics via ID number (links to Client via ID number). Implied link back to contract through categories link.

I don't have this set up because I cannot guarantee that a client will have same case worker; the case worker is identified using per-visit records.

Company --> Agents (if they can cross then via junction; otherwise parent/child)

Company --> Contracts via junction

Company --> Company via self-join if there is one prime and some number of sub contractor companies.

Don't think applies as this is for one company and is for its internal use only?

~~~~~~~​

Now, the idea of having a table of events is intriguing. The more I think about it, I can visualize associating events with an attribute.

Therefore, in tblAttribute, I would enter those data:

AttributeID    AttributeName
1                 CommunicationPreferences
2                 ReasonableAccommodations
3                 Case
4                 Topic

and in tblEvents, it would be structured so:

EventID
AttributeID (foreign key to tblAttribute)
Event
.... some more fields describing what events should handle, and what should it output


so the data entered would be:

EventID AttributeID    Event
1          3              Open
2          3              Close
3          4              DateIdentified
4          4              DateResolved
5          4              BarriersRemoved
6          4              IssuesREsolved

Note that first two attributes has no associated events; this is deliberate because they are "ask-once, never again" deal; if I had a need to maintain a history of what was recorded, then yes, they will need events, but I don't so they stand on their own.

If this is the structure you envisioned, this does solve the problem of "grouping attribute" I described in my initial post. I simply create a junction table that "instantiate" the events/attributes whenever it needs to be answer. (Forgive me if I'm misusing OOL paralance where it doesn't belong; it just made sense to me... I hope)

The junction table would probably be something like:

tblPersonRoleAttributes
PRAID (PK)
PersonID
RoleID
AttributeID
Answer

tblPersonRoleEvent
PRAID
EventID
EventDate 'Maybe unneccessary if the Response itself is a date?
EventResponse

Therefore attributes can be "grouped" through events... is that what you are trying to convey?

But I'm still stumped on how I would identify which Topics would be related to what Cases? That is, two attributes that needs to be related to each other? I am having trouble seeing how events would help me here.

Maybe it would be simpler to have a extra field or two in tblEvent that specify whether it has a attribute that it should "fire", and how it should be handled, and write a generic function to process the firing.

Or maybe it's late and my brain has melted into a pudding.

 

[The_Doc_Man]

Or maybe it's late and my brain has melted into a pudding.

I am reminded of the classic exchange between Donkey and Shrek in the first Shrek movie (slightly misquoted here):

Shrek: Ogres have layers like an onion.

Donkey: But I like parfait!

Design issues for a real-world government contract should really make your head into pudding or parfait more than once or you haven't been thinking hard enough. It is part of the government style of deigning to grant you contracts with more rules than a Byzantine bureaucrat and fewer useful definitions than Ambrose Bierce's The Devil's Dictionary. Having worked around these folks for nearly 20 years, I can state with some certainty that the contracts people don't think they've done their jobs correctly unless they give more headaches than they get.

attributes can be "grouped" through events... is that what you are trying to convey?

That was the general idea. From what you were describing in English, it seemed that the two were conjoined in some obscure way. The best I can say is that this is probably one of those higher-level NF resolutions. 4NF or 5NF, maybe. The problem, of course, is that as the NFs get to higher levels, "seeing" the resolution as such is correspondingly tougher to envision because of the multiple layers of relationships. (So the Shrek misquote about layers wasn't really irrelevant.) It's even TOUGHER for us because you know your problem and I don't, and we don't even speak the same sub-dialect of govspeak because each government department has its own favorite flavor.

I believe it was Oscar Wilde who described Americans and the British as "separated by a common language." Well, government departments are often the same way.

 

[Banana]

The_Doc_Man, thanks for the rapport.

I'll go back and read over the normalization some more and maybe get a better idea of what level of normalization I'm needing here and visualize it better.

Will come back with more specific questions if I have any.

 

[The_Doc_Man]

You're on your own for a week. Wifey and I are heading out of town.

 

[Banana]

All righty. After some consideration, I decided that I'd consign to create an artificial limit of 7 level of depth for self-joined queries after talking with other folks and being told that SQL doesn't really do that well when you want it to handle data with unknown depth as it cannot do any recursion automatically.

However, I wondered if the structure proposed would at least help to emulate the grouping and parents of the groups.

tblDetailGroup
DetailGroupID (Autonumber PK)
ParentID (FK to tblDetail's PK)
GroupID (FK to DetailGroupID)
DetailID (FK to tblDetail's PK)

ParentID is the only optional field. In the relationship, there'd be two instances of tblDetailGroup and a tblDetail. The relationship is as follows:

tblDetail.DetailID 1-M tblDetailGroup.DetailID
tblDetailGroup.DetailGroupID 1-M tblDetailGroup_1.GroupID
tblDetail.DetailID 1-M tblDetailGroup_1.ParentID

Will this work for modeling something where I want to be able to "group on fly" with unknown depth. The artifical limit of 7 is because of how I intend to write my query (e.g. I will only go only 7 self-join deep from 1st level) and should be good enough.