OK, we've been wrestling with a normalization issue, with me being a sort of rabid normalizer and my boss being the voice of reason, so I thought I would put the question up here, broadly:
What are the limitations of normalization in A5?
The specific situation that arises can be seen by this example: We have a table that contains information about "projects". Each project is associated with a "contact". The contact's name is vital to the project, and I managed to make the name appear by using lookups, even though I'm storing only the contact code.
So, if "Sam Jones" has an ID of "0001", projects associated with Mr. Jones have only the "0001" code. Through the magic of lookups, this appears as "Sam Jones" in all the forms and browses based on the project table (even though, again, only the "0001" is stored in the project table).
This is wonderful. Normalized. What I was working to achieve. You can even do a right-click-sort on "Sam Jones" and the column will come out in alphabetic order (sorted by "Sam Jones" rather than "0001"). But there are problems.
All or most of the underlying DB functions (except the right-click-sort) mentioned above seem to function on the "0001" instead of the "Sam Jones". In particular, the built-in "Find" function operates inconsistently (from the user's standpoint).
We've thought of a few ways around some of these problems. I suggested, for example, that we build the index for the field based on a function that does a lookup--so that "0001" really does become "Sam Jones"--but initial attempts at this seemed to indicate that using a function in an index was a bad idea. ("Bad" as in "unreliable" and "prone to breakage".)
We could also roll our own Find routine and do some sleight-of-hand, though this becomes a lot of work, and presents us with considerable work to duplicate the built-in feature.
Or we could just scrap normalization and store "Sam Jones" alongside the "0001". Then, whenever the contact database is changed (say, "Sam Jones" becomes "Sam J. Jones"), we go through and update the projects table as well as the contacts table.
This last makes me shudder. Normalization--3rd normal form--is a Good Thing. But if it can't be done reliably, it can't be done. So, how do y'all handle this?
Thanks in advance for any feedback.
What are the limitations of normalization in A5?
The specific situation that arises can be seen by this example: We have a table that contains information about "projects". Each project is associated with a "contact". The contact's name is vital to the project, and I managed to make the name appear by using lookups, even though I'm storing only the contact code.
So, if "Sam Jones" has an ID of "0001", projects associated with Mr. Jones have only the "0001" code. Through the magic of lookups, this appears as "Sam Jones" in all the forms and browses based on the project table (even though, again, only the "0001" is stored in the project table).
This is wonderful. Normalized. What I was working to achieve. You can even do a right-click-sort on "Sam Jones" and the column will come out in alphabetic order (sorted by "Sam Jones" rather than "0001"). But there are problems.
All or most of the underlying DB functions (except the right-click-sort) mentioned above seem to function on the "0001" instead of the "Sam Jones". In particular, the built-in "Find" function operates inconsistently (from the user's standpoint).
We've thought of a few ways around some of these problems. I suggested, for example, that we build the index for the field based on a function that does a lookup--so that "0001" really does become "Sam Jones"--but initial attempts at this seemed to indicate that using a function in an index was a bad idea. ("Bad" as in "unreliable" and "prone to breakage".)
We could also roll our own Find routine and do some sleight-of-hand, though this becomes a lot of work, and presents us with considerable work to duplicate the built-in feature.
Or we could just scrap normalization and store "Sam Jones" alongside the "0001". Then, whenever the contact database is changed (say, "Sam Jones" becomes "Sam J. Jones"), we go through and update the projects table as well as the contacts table.
This last makes me shudder. Normalization--3rd normal form--is a Good Thing. But if it can't be done reliably, it can't be done. So, how do y'all handle this?
Thanks in advance for any feedback.
Comment