Levels of Abstraction and Database Design (1 Viewer)

[Banana]

Today, I spent the bulk of my time reviewing and refining the database design, which is quite a feat when I'm trying to give it enough flexibility for several variables that may come in play.

Background:In this thread, there was a side discussion about how to handle situation where you do not have complete certainty about what attribute you want to assign to an entity.

I initially used a series of one-one tables to represent otherwise incompatible attributes assigned to entity (in this case, the entity is a person, and the attributes describe something about the person's role). After analyzing the hypothetical personnel database with the problem in that thread, The_Doc_Man made a suggestion of enumerating the attributes as records in a table rather than as fields and demonstrated that I had de-normalized my design when I used the one-one tables.

As I turned to the problem of how I would adopt The_Doc_Man's proposal and make the entry forms work, now that I no longer have fields, I had an idea. A very dangerous idea. Consider this: I can never be sure what attributes I will require from my users since the roles may be modified on a periodical basis, not to think of possible new roles added or taken away as my company's services expands. We have several contracts, some which are concurrent, allowing for overlap of our services, and provide different services, depending on what contract we have at the time.

Therefore, I thought of de-normalized temporary table that generate the fields that will answer each attribute given to a role, *and* define the field's properties (such as required, validation rule, validation text, caption, and input mask) as the temporary table is being created, then bind a blank form (probably a datasheet for presentation reasons) to the temporary table which would enforce the data validation with meaningful data, appropriate input mask and descriptive captions. Once the data entry is done, the de-normalized table then feeds into the junction table that represents "answers" to each attributes for a given role for a given person (and can be done same for every service for any contract as well), re-normalizing the data.

So, if I'm not mistaken, this means I've abstracted away the information about a role (or any given entity that may have its attributes stored in a like manner). Therefore, modifying existing roles and adding new roles is now a trivial problem.

But I said it was dangerous because I know that some has asked for "dynamic tables" in past as a means to get around the normalization and would definitely want to consider the possibility that the idea could be very bad as it'd just obfuscate the even more deeper flaws with your database design.

Then there's another consideration. Since I've abstracted away the attributes of an given entity, it seems perfectly logical that I should also abstract away the record's reference within VBA. That's where I got confused. When I was thinking about how I would refer to certain records in lookup tables, I thought of creating a list of global constants (I say global because I'm not 100% sure yet how broad scope I will need at this point; it may end up as a private constant but I digress) but this presented me with a problem: If I'm going to list constants of ID for lookup records, why even have a lookup table at all?

When I tripped over that question, it bought me to another thought; any Access MVP will tell you that Access is not a appropriate program if you want or need a n-tiered architecture, as Access wraps together the presentation and data access layer. If I'm now abstracting away the entities and thus require my forms to be able to handle such degree of generality, would I be better off with a proper n-tiered program to handle this?

So to wrap my long post, the two questions needs to be answered:

1) Is it appropriate to abstract away what otherwise be a "table" when you cannot be sure about what should constitutes its fields?

2) If a high level of abstraction is needed, is Access capable of handling such, and exactly when we should be considering other solution?

 

[boblarson]

Banana:

I would say that if you have entities which have an unknown number or type of attributes, you could move to having the attributes themselves defined in a table so that you can add as many as you need whenever you need them. In fact, that is just the same answer I provided to someone else (actually a couple of them) and one of the posts is here - which might help (or it just might confuse things).
http://www.access-programmers.co.uk/forums/showpost.php?p=606861&postcount=40

 

[Banana]

Great; you just proposed exactly same thing as The_Doc_Man. That's very good thing as this gives me confirmation that this is really the way to go.

However, I realized that I obscured one of my questions in my large post: Would using a denormalized table based on the list of attributes be a good idea for purpose of data entry, especially with specifying data type, caption, required property, validation rule and text for the attributes?

 

[boblarson]

I'm not sure why it would be denormalized if you are using a table of attributes. I'm sure you could use dynamic form work to enter the attributes, but it actually may not be necessary. I realize that this is a simplified sample but here's a sample of an example:

 

[Banana]

I'm not sure why it would be denormalized if you are using a table of attributes.

What I meant was I'd query the attribute tables for a given role, and use a make table query to create a temporary table with every attribute as a field for data entry then bind it to a dynamic form, as you alluded to.

I realize that this is a simplified sample but here's a sample of an example:

Sweet! I'll take a look at it when I return to my work tomorrow!

I was a bit concerned that the idea of using temporary table/dynamic form would be overcomplicating things.

 

[Banana]

Another concept I"m trying to develop: How to abstract away the lookup tables that was used for "coding" the various roles.

It would be impractical for several reason to create one generic lookup table, I think. Beside, I can always add new lookup tables and archive old tables.

In Attributes table, I've added an field, "LookUpID", which isn't technically a key, but rather an identifier to which lookup table the attribute will use. I then reason that since the Person/Role/Attribute junction table stores the "answer" to attribute, I can store lookup table's ID in the answer field, and since attributeID is stored as well, use it to tell me what lookup table is being used and set everything accordingly, then I'll get the correct entry for later views.

Unless I've missed something, this seems reasonable solution to me. But the problem is sometime I need a junction table between the role and "code" lookup table as the relationship is many-many. How, then, do I abstract away the junction table?

My initial thought is that the junction table should be related to Person/Role, but not attribute, then add two more fields: LookUpTable (stores the name of LookUp tables that is related as many-many) and LookUpID, in similar fashion to above solution.

Does the proposed solution sounds reasonable? I hope I'm not subtly denormalizing things here...

 

[Banana]

I'm not sure why it would be denormalized if you are using a table of attributes. I'm sure you could use dynamic form work to enter the attributes, but it actually may not be necessary. I realize that this is a simplified sample but here's a sample of an example:

Now that I've seen your proposal- yes, datasheet is certainly a good solution and I do plan to use this in some of scenarios.

However, the idea I had in my idea was to be able to enforce validation rule, give out custom text, require text entry at field level, which was why I thought of using a temporary table listing a set of attributes in a denormalized fashion, with each attribute-field properties defined thus.

Of course, if I was going to use a temporary table, I'd have to use dynamic forms, and abstract away the bindings, <insert in some more abstraction here> and end up with essentially unbound forms for which a n-tiered architecture would be more appropriate.

I suppose the issue is more of a data presentation... I'm not sure.

 

[The_Doc_Man]

Banana, here is what I did for a CD (musical) database. It is relevant at least partially.

First, the CD table itself lists a PK Autonumber. CD's title. The primary label (Columbia, RCA, Virgin, Decca, etc.) There is a serial number. There is a number of tracks and a total run time. Technically, the total run time is a crock since it is hand-entered and sometimes absent. But hey, I never claimed perfection. There is a Genre code that tells me whether it is Country/Western, Classical, R&B, Electronic, Sound Track, Broadway Stage Recording, any of several Foreign sub-categories, etc. The code is a drop-down that looks up CDGenre. There are codes for Mixed, Mixed Classical, Mixed Non-Classical, etc.

Second, there is a (child) many/one table CDAttr that links back to the parent CD via the PK. It has a secondary list of attributes that can be chosen (and CAN, not MUST, is the operative word) via drop-down. The attributes record includes a track number. Each such record includes the parent ID, track ID, attribut ID (essentially, a short code), and the attribute value. The codes allow me to show Selection Title, Artist, Lyricist, Composer, Conductor, Instrument, etc. The codes translate via a drop-down through CDAttrName in case I forget what a given code means. I can also include a CDAttr record with track 0 (meaning the WHOLE CD) to show that it is a single-artist CD or single-composition. The codes don't change, their scope does.

The point of this is that the CD's stored attributes vary from CD to CD (not just Role to Role, as in your case). I don't have to put ANYTHING in the CDAttr table - but if I want to, I can extend the attributes in very high detail down to the track level. Other than those attributes that are common to ALL of my music CDs (title, publisher, serial number, number of trackes, genre), ALL OTHER ATTRIBUTES are optional and variable at the individual CD level. It is up to me what I store.

Once I had all of that CD stuff in place, suitable JOINs allowed me to list CD (as a header/footer type thing) with attributes (as detail records) on a report. BUT I can turn that around and sort by artist name to build a report showing which track and which CD contains, say, "Arthur Fiedler" or "Aerosmith" or "Charly Daniels" - by just following a BACK-LINK to the parent CD and using the in-table track info to decide whether it is one track or the whole CD.

No denormalization occurs here. Other than the track-time stuff, but at the time I didn't have a way to properly enter that. It's an OLD database, first implemented in AC v2. Yup, pre-AC97.

Now... how would you implement a "mandatory" attribute? Well.... put it in the parent or see case 3 later in this paragraph. How would you implement "optional" attributes? Put them in the child table. The third case, mandatory for one role but not for another, has to be done via a table of roles and possible attributes, with a flag that says THIS ATTRIBUTE IS MANDATORY. Then on the form where you fill in the attributes, don't allow the form to close if any mandatory attribute has not yet been properly answered. I.e. count the mandatory attribute value records you have filled in and the attributes marked as mandatory for that role. If they ain't the same, you ain't done.

 

[Banana]

Good, good.

Now here's my scenario.

I'm trying to consider how I should allow for various clients for various contracts.

The beauty of the design is I now no longer need a table of Clients. Instead, in Role table, I create a Client of Contract X for Fiscal Year 2007-2008. Now, Contract X will have its set of "required" attributes which I need to pass to the Client. Now when the contract ends, and we start a new fiscal year, the government bureaucracy decides to changes something around, so implements a different set of required information (for most parts, they're same, in which I can just re-reference in a junction table), so I just add the new "attributes" and "role" (e.g. Client of Contract X for Fiscal Year 2008-2009) then pesto, I've updated all I need to know about a given client for a given contract for a given fiscal year. (that was quite a mouthful!)

So, the "required" attributes can't always belong in the parent child, as they're dependent on what contract/fiscal year we're talking about. Does that clarify my plight?

 

[The_Doc_Man]

You must identify the place where the attribute goes. That is where it goes, regardless of extraneous dependencies. You don't care WHY that attribute is here for X but not for Y in the same table. If the attribute goes with SOME members of the table, you put it with (under as in parent/child or one/many) the item for which it is an attribute.

When you put all sorts of spot constraints on anything, you rapily approach the point where VBA is going to be requried for your really strange attributes or dependcies.

 

[Banana]

Hmmm. I'm a bit confused.

Let's see if I'm following you.

Roles may be constrained by temporal considerations, (e.g. fiscal year) and may be related to certain contracts. Therefore, I create some generic roles, and a table of contracts with its term included. I then create a junction table between a role and contract to define what roles a contract may provide for. Because contractee (e.g. government bureaucracy) may decide to do something new, they mess around with the set of information they require us to ask a given person.

So while we may have same roles for contracts (but we can't be 100% sure that they won't add, drop or change any roles covered in next rounds of negotiations). Therefore, another junction table is needed to enumerate the attributes of a given role for a given contract:

ContractID
RoleID
AttributeID

It would then follow that I should create another junction table that's four way for actual person:

PersonID
RoleID
ContractID
AttributeID
Answer

Did I get that? Or am I overcomplicating it?

 

[Banana]

Another thing I'm wondering why is nobody talking about using a class module and black-boxing otherwise complicated control set-up? I'd think that if I were to have a cascading combobox, I'd want to create a class module, build variables to specify what tables and fields both comboboxes may use, specify methods to be executed for a given action so in an AfterUpdate event, it's just one line of code to call the class module's method.

I did a search in the forums about past threads about class modules, and found nothing substantial. Is there any particular why we aren't using class modules?

 

[The_Doc_Man]

Did I get that? Or am I overcomplicating it?

Only you can answer that. The deciding question is whether your spot exceptions are role-related, person-related, or something-else related.

The attribute belongs as a child of the role. The only reason you would add in the person ID is if a person could bring to the table attributes not part of the role. If you draw a dependency diagram and the role never depends on an individual person, only with the role itself, then the attributes belong to the role and not the person.

As to why I have not discussed class modules: The problem is consistency. If you did all of this in a class module, where would the associated data go? You first have to have a meaningful DB structure before you can start mucking about in it. About the only thing I would use module-based code to do here is to decide when an attribute is mandatory if there is ever any case where there is doubt. The module-based functions would decide whether the attribute is going to be applied or skipped. Otherwise, the relationships are the thing with Access. (It IS, after all, a relational database!)

Your comment "Rules may be constrained by temporal issues" - means that if the way of expressing the relationship becomes too messy, you would make the role optional but put the black-box techniques to work to do the serious logic of whether the attribute is required or not. OR you can make two attributes that are almost the same, differing only by their time sensitivity. There IS that school of thought that says if the two records differ even in one bit, they are different and should be stored uniquely.

 

[Banana]

Oh, dear. It seems that I've been sloppy with my tangent Re: class modules. I wasn't thinking of using them for roles' attributes but rather the controls on form to present the attributes in a logical and pleasing manner to my users. I was trying to visualize if Access is capable of handling high level of abstraction for my proposed design to work, and that's why I thought of class modules to black-box the controls, especially cascading comboboxes and two transferring listboxes, and the likes.

Mind defining "spot exception"?

This is getting quite heady for me, as I've walked in the trap of that three legged tables and I'm trying to abstract away the variables so my database can handle any contracts for any fiscal years with any requirements for any roles or services provided.

The dependency diagram may help a bit. I remember you suggesting post-it notes and a whiteboard as well. I've actually used that whiteboard/post its last Thursday to help me conceptualize how I'd manage various services defined by various contracts and how to line it up with a given person/role.

Still working on this.

 

[The_Doc_Man]

OK, "spot exception" - in any database, there are rules. To some people, rules are made to be broken. (For instance, at POS terminals, the price override when the store has a "we will beat anyone else's price by X %" guarantee so there is not even a discount code for the proper amount.)

In your case, you are agonizing over how to define whether the attribute is a property solely of the role or the person - or if there is ever a case where BOTH bring something to the table. (Egad, another ugly pun... I'll have to watch that.) Back to the theme: If you have an attribute you want in the list of attributes associated with a role, it would seem that it can only be mandatory or not mandatory. But the question is, can there ever be a variable reason for it being mandatory? The "spot exception" cases relate to situations where you start with an optional attribute but might have to upgrade it for some cases - and the rules for the upgrade to mandatory status can be as wild as "who is the employer" or" who is the employee" or "which contract is paying for this role"?

If you find that it becomes a nightmare to figure out the table structure, because of exceptional cases in the rules, you can take the black-box approach to do the logic that decides whether the attribute in that particular combination of factors has become mandatory. Now, if you do this, you are admitting that the business rules you are trying to model are intractable or at least are so Byzantine as to defy ordinary query logic. We all hope that such cases are rare - but sadly, they are not as rare as we would wish.

~~~~~~~~~~~​

Using VBA to make your form pretty is a good idea. I've done that more than once, including little bitty traffic signals that give you Red Light when they are busy, Green Light when you can enter a new record, and Yellow Light when you are working in a situation where you cannot take certain actions on the form until you clean up something, like inconsistent data.

I've also used VBA to make buttons appear, disappear, and move around on the form so that all functions are (in my case) on a single row and left-justified. That's just playing with the Top and Left properties of each button to move it and use the .Visible, .Enable, and a couple of other properties to disable buttons you aren't supposed to use.

I NEVER use the "real" progress bar because I can make prettier - and more precise - progress bars out of rectangles where I muck with the border, background, visible, and width fields. You can make the back color pulsate. You can get very precise control over progress bar size, since you get something like twip-level inputs on the bar length. On a big display, a twip is a fairly small unit.

But here's a scheduling hint about making the form pretty. Leave that for last. Once the form works right, then and only then, knock your lights out.

ALWAYS remember that for art, it is "form before function." For business, it is "function before form." If you make the prettiest form in the world but it is hard to navigate or otherwise use, whom have you served? And if it is a plain-Jane form - but a caveman can use it - then maybe you have a career in building insurance web sites? (For our UK friends, that is a nod to a current series of commercial messages on our telly.)

 

[Banana]

Thanks for defining spot exceptions.

After a thought, I suppose four way junction tables I described will be sufficient to satisfy the needs of maintaining different attributes that are dependent on what roles a person has, and what contract provides funding for a given roles and consequently requires something about this roles.

But one thing I'm just little unsure.... In the setup you discussed with me in other thread, there's Person table, Role table, and Attribute table, RoleAttribute Junction, then PersonRoleAttribute Junction (answers). If it's going to be four ways (e.g. PersonRoleAttribute -> PersonRoleContractAttribute), I know that I will need a ContractRole junction, but not sure if I'll need a ContractRoleAttribute Junction?

That said, I can't help but feel a silly about the whole thing, as it increasingly seems to me I'm trying to making a meta-database. This bothers me a bit here and has to do somewhat with my earlier tangent above: many people tells that by far the biggest benefit of using Access is bound forms. I agree. Nothing wrong there. But by binding *specific* fields/tables, it's easy to end up hard-code everything, even though many of common functionality could have been black-boxed. Yet, it seems that based on my search, class modules is seriously underappreciated, even if VBA isn't complete object-oriented language and many code samples posted by various users are specific for one situation and just one situation. This is what led me to question whether if I'm wanting high level of abstraction for my data as I have so many variables, Access is a wrong tool for me? Did that make sense?

 

[The_Doc_Man]

The question is quite sensible. There comes a point where you simplify your database rules just a little in the queries and instead put code in the VBA segment - the class module - of that form.

The reason you would want to be excruciatingly precise about the table relationships is if you ever allow someone direct access to queries or tables, you need some rules in place. With a form, you have the aibility to execute all sorts of hidden rules that are in the event code, not the tables.

I would be sorely remiss if I left you with the viewpoint that EVERYTHING can be done through SQL. Straight SQL will always have its limits. That is why knowing about VBA can help you cover a multitude of sins that a table could not cover. Stated another way, it is a poor mechanic who is unwilling to use every tool in his arsenal. Which is why in the other thread, I told RoyVidar and CraigDolphin that it was OK to use NULL - if at least you used it to mean what we have agreed it to mean - namely, when you see a NULL, you see NOTHING. And everything that NOTHING implies. (Boy, oh boy! Is that ever a semantic nightmare of a sentence...)

 

[Banana]

Ah, but if I used forms and the codes behind the form, wouldn't that be essentially hiding my otherwise piss-poor database design? After all, I got away with that three legged tables for a year because most of rules were enforced in forms. Not to mention numerous times of re-inventing wheels when the database engine could have been perfectly capable of maintaining data integrity.

Even though my design was flawed, I had all relationships' RI enforced, and if I can make it stricter, I did so, reasoning that by being anal-retentive and even putting up with extra headaches I have in setting up the forms, it should be self-maintaining (an objective for an ideal database, no?), and for large parts it was. Except for that ugly fact that I don't have just one contract and that the bureaucracy behind all contracts are not necessarily inclined to keep all of contracts' terms intact. They like to mess around with the requirements, thus I am in the position of needing to go higher on the ladder of abstraction to be able to encompass *this* possibility.

Another aside: I considered why/how I got led to thinking that having three legged tables would be appropriate. Here's an example; let us consider a standard order invoicing database. We have a table of Products. Assuming the fields within has all the necessary information, it can easily cover anything that we would sell, even if we don't think of it as 'products' (e.g. livestocks? pets?). No further level of abstraction is needed here, and all other tables such as orders and customers seems sensible for this database's needs.

Now I want to try something. Suppose that our company does not merely sell products but perhaps may provide consultancy, perform contract services among with selling orders. To put this in more concrete terms, this company could be a web hosting company who sell a range of packaged website services (e.g. shopping carts, forums PHP, and the like), provide consulting and technical support for websites, and of course, provide web hosting.

Does that mean the table of Products is now inappropriate for other services that aren't 'products' in the sense that the attributes of consultancy and other services is now radically different from what you expect of products?

If so, wouldn't it be just fine and dandy to put them in their appropriate tables; (e.g. tblConsulting, tblServices) which can then link to a common tblOrder or other junction tables?

See, that's why I'm confused here. Based on what I've seen in other databases, lot of tables are about a specific class, but rarely about a broader class that can encompass several classes within. We are used to see table of Products, not a broader table of Marketables or something like that.

Did that make sense?

 

[The_Doc_Man]

wouldn't that be essentially hiding my otherwise piss-poor database design

A little VBA covers a multitude of sins. However, it is not ALWAYS a crutch. When your business rules make it easy to codify activities using tables, relationships, and action queries, you don't need VBA. When you run into that oddball case - such as, e.g., a "grandfather" clause that exempts someone from a particular zoning requirement, if you were doing a county zoning database, there comes the question of exactly WHAT is being allowed. When you have a FEW exceptions, you let them slide in the DB with one special mark that says, in effect, "YES: Ordinary processing does not work for this record." So you process everything else, then go back and hit the exceptions with the VBA.

Suppose that our company does not merely sell products

When that happens, your product is HOURS. (More specifically, person-hours) where the inventory numbers lead to the charge rate for a particular job category and the auxiliary data of the inventory record names the type of individual. For my employer, it is "Operator I", "Network Engineer III", "Systems Analyst II", etc., each with a different overhead rate, each with an expected burn rate (nominally 40 hours per week for full time support) and burdened overhead rate to include expenses and profits.

Your accounting persons advise you of the selling price of an actual product based on how much it costs to obtain it (= salary of the individual), overhead costs to maintain records, incremental contribution to insurance (liability vs. health), specifications (vs. skills), profit margin, etc. So you identify the job skill level, then charge in units appropriate for the contract. Just as though you sold hours and they bought 40 per week for a while.

Even if you don't buy that because you think the services table should be somehow different, you can still use a UNION query to unify the charges. The number of hours becomes the quantity, the hourly rate becomes the price (and you carefully note that no discount rates are available...), etc. etc. You can itemize this by person or by number of people of each "skill set."

There are also "package" deal possibilities where when you buy the Super Foonley Whiz-Bang Adapter you get 1 week of classroom training for up to 10 people. And of course, your accounting wonks have priced this to include books, travel & accomodations & per diem for the instructor, plus the hourly rate for the instructor during the class, etc.

Labor is not really that different than any other business billing transaction. It is a matter of realizing that there is more commonality than might first be obvious. But the common threads are there.

 

[Banana]

Here's something that maybe will help clarify why I'm struggling with this.

Let's consider a standard invoicing database. Normally, you have table of Orders, Products and Customers, with the needed junction tables. Here, the level of abstraction is just right. Looking just at the product table, you've already generalized it sufficiently to work with just any products, even if we might be be discussing about livestocks, pets, raw materials, or other things that we may not customarily think in terms of "products." But this works (provided that the fields within the tables can encompass all of those objects).

But suppose that we have a company that does web hosting, provide website design consultancy, technical support on top of selling packaged products such as online shopping carts, forum software, and the like. Here, we can't exactly cram consultancy, technical support, and contract services into products as the attributes are too different to be incompatible.

The question then, is it appropriate to create a table of Services to encompass consultancy, technical support and other services, which all can use a common Order table to list which customers ordered what products/services and what quantity (e.g. counts for products, and hours for services)?

Based on what I've seen from others' design, I'd imagine many would adopt this two-table design. I've seen many tables that are about a class of objects but almost never seen a table that describes a class of classes, which what I'm going to do with the Roles/Attributes.

After all, that three-legged tables design I had worked just fine for the contract year. It just couldn't stand the fact that we will have other contracts with its own set of terms and stipulations and even for the same contract, the terms can easily be changed from fiscal year to fiscal year. I suppose same can be said of "Products/Services" for the web company I described above. It'll work fine, *provided* that the company doesn't decide to expand its offering beyond what can be encompassed within products and services. Otherwise, it's a major re-write.

The second issue is that I've never seen a three way junction tables, and I just proposed four way junction tables. This more or less runs contrary to what I've seen in an "ideal" database model. You're probably heard of two models; snowflake and star schema. Right now, my model is nothing like that and it's not even remotely hierarchical, as Person exists in its own right, and same is true for Roles and Contracts.

For some reasons, I've come to think that if I can draw my relationships in such fashion that I can trace the line and end up back at the same table, I have a circular reasons. Of course I saw that it wasn't the case when we worked on and came up with the class/attendance schema, which I've posted up a prototype in other thread. Yet, I'm not fully comprehending how we can not have a model remotely patterning that of a star or snowflake.

This is probably why I am finding the new information to date however counterintuitive; that I've come to expect generally hierarchical structure as being better for any relational model, that low level of abstraction is easier to work with.

I hope that was clear....

PS. Wow. I thought I had lost my earlier post for some reason but apparently not... You can ignore the Product/Service scenario as the earlier post covered it already....