Levels of Abstraction and Database Design

[The_Doc_Man]

to see what they have to say about class modules.

It isn't that class modules are underrated but that so many books on Access are overrated. Search my posts to see if I've EVER strongly recommended a book on Access. I dare you. I double dare you. I triple-dog dare you. (But you don't have to put your tongue on a frozen flagpole.)

For those scratching their heads at that last, see the movie A Christmas Story - the one where Ralphie wants a Red Ryder BB gun for Christmas and EVERYONE says "You'll put your eye out." It is an absolutely hilarious little movie about growing up in USA elementary schools with bullies, harsh teachers, and kids' fantasies. Shows up on cable channels every year around the holidays.

 

[Banana]

VBA implements these data types. Jet does not. And therein lies the problem with using Ken's suggestion.

Apparently, if you declare a public function in VBA, you could have Jet access the function within the query to get the dynamic pointer we need.

Here's an example from Alison Baltier's reference book (that's one I consult more frequently. Unlike that other two-paragraph-devoted-to-class-modules-out-of-the-entire-book book, it's actually useful):

SQL:

SELECT tblEmployee.EmployeeID, tblEmployee.FirstName, tblEmployee.LastName, tblEmployee.Title, Initials([FirstName],[LastName]) AS EmpInitials
FROM tblEmployee;

Function itself

Function Initials (strFirstName As String, strLastName As String)

Initials = Left(strFirstName, 1) & "." & Left(strLastName, 1) & "."

End Function

Does it then follows that we can use functions to enforce rules within action queries? Maybe even act as a pointer that is provided in VBA but not otherwise supported in Jet?

 

[Banana]

Some loose ends in the thread that needs to be wrapped up....

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...

Also, now that we know that it's okay to trace a relationship back to original tables, provided that the whole setup is correct. I've now lost my customary definition of circular reference as the tracing idea may not be always true. When does such set of relationship, especially for more than two tables, becomes circular and therefore unsolvable?

 

[The_Doc_Man]

Does it then follows that we can use functions to enforce rules within action queries? Maybe even act as a pointer that is provided in VBA but not otherwise supported in Jet?

Best answer is "Try it." But I'll tell you that I have my doubts. See, I don't think the SELECT clause allows the indirection - only the WHERE clause. If what you want is a dynamic search and you can drop a function in the WHERE clause to do what you want, should be OK. But what won't work (I think) is if you had to dynamically declare the names of the fields to be selected by the SELECT portion of the query (or the equivalent for an action query.) It is THAT narrow segment of syntax that I think is not comfortable with deferred selection.

Stated another way, I've seen many PARAMETER queries work when the parameter was in the WHERE section but have never seen one work when it was in the SELECT section.

But hey, I've been known to be wrong before.

It also occurs to me that in a pure VBA environment, you can build a dynamic query string from information such as Ken mentioned. But you have to do it in two stages - build the query then use it. There would be no way for the query to be dynamic while actually running.

Does that 'splain it better?

Regarding the other loose end: I'm having trouble picturing that level of abstraction in my head. I may have to back off for a while. The company's rule is that I can do daytime surfing only when nothing major is going on. I have a big security upgrade coming down the pike. As soon as I get the go-ahead for it, I'm going to be sloshing through the swamp for a while, trying to avoid too many 'gators.

 

[Banana]

Yeah, I'll experiment with it a bit and report back later.

Good luck with the moshin' around!

 

[Banana]

Maybe anyone else has a suggestion about the loose ends I listed above?

 

[KenHigg]

Not me -

 

[Banana]

As it's a new page, best to re-post in case anybody can give a suggestion...

Some loose ends in the thread that needs to be wrapped up....

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...

Also, now that we know that it's okay to trace a relationship back to original tables, provided that the whole setup is correct. I've now lost my customary definition of circular reference as the tracing idea may not be always true. When does such set of relationship, especially for more than two tables, becomes circular and therefore unsolvable?

 

[Banana]

Replying to my own questions regarding whether how to dynamically create a lookup table for a attributes.

I'm stickler for strict definition whenever possible. I don't like my tables hanging around freestanding with no relationship. I don't want it.

But how to associate potentially multiple lookup tables to multiple attributes?

Since I know that no matter what, *any* given attribute I come up with will be a one side or participating in a many-many relationship with lookup tables, but never a many side in a one-many relationship. (This would kind of defeat the purpose of lookup table, though).

Looking at my other relationships, I suppose I could just create two more tables. Let's call one LookUpClass. This defines what kind of lookup this is. It will be a one side to another table called LookUpListings. This stores *all* possible lookup values. All values must belong to one class. When the attribute is prompted to my users, I can just query according to what Class the attribute want to use to return the set of values to select from.

As for many-many relationship, it's just one more table; create a junction table between the LookUpListings and Answers table. This would enable the attributes to have both one-many and many-many relationship. But does that create potential problems to have a "duplicate" relationships?

Furthermore, lookup tables are supposed to be stored locally for performance reasons. Making them dynamic would necessitate that they be stored on the backend, which was why I initially suggested free-standing lookup tables while attribute table stores the ID identifying the LookUp table.

Any inputs?