William,
I had a client that started adding a character in front of employee names that no longer were with the organization and it created a whole lot of problems with searches, report and selection sorting and so on. Now, every table in which records might become inactive has a column that indicates the status. For employees it might be as simple as A Active I Inactive For equipment we have: IU In Use LM Lost/Missing SN Stolen ST Surplus Traded SS Surplus Sold SO Surplus still Owned SA Surplus Auctioned RR Rental Return PP Planed Purchase Whenever data is displayed for the equipment table, by default only records with status "IU" are displayed; however, forms have a button that will allow displaying all records. The user also has the option to select specific "status".enhanced DBGrids make the job a lot easier. Javier, Javier Valencia, PE 14315 S. Twilight Ln. Olathe, KS 66062 Home: 913-397-9605 Cell: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of William Stacy Sent: Wednesday, February 22, 2012 11:08 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Too relational? I love this discussion. My posting has been a bit clumsy, so for clarity I'll offer a couple of points here about how my system is structured: I have a pat table (from patient, since I'm in the health care field) which has evolved into more of an identity table, as it contains both people and organizations, active and inactive. I differentiate humans from orgs. by the presence or absence of a first name. Last name field is used for both company names and individual last names. All name searches start with the last name and progress as needed to first names, account numbers, etc. I differentiate deceased humans by placing a Carat (^) in front of the first letter of the last name, and could do the same with defunct orgs. but haven't needed to do that. I also move them from their family into the family of the dead, an increasingly large family as I get older. But their financial/medical records remain linked to their name via their autonumbered I.D. #. The only time I delete a record is when it is an accidental duplication, and then only after carefully searching for any links to transactions, etc. With terabytes now common, I see no reason to delete any files for any other reason, ever. For much the same reason, I now see no reason to delete an address or a telephone number. People change, orgs. change, but addresses and phone numbers don't, or shouldn't. I'm questioning my own wisdom here because it will indeed require a big schema change for me and I do know that is time consuming. All this in preparation for the final migration to 9.5 all the way from 4.5++. A daunting task that is looking more possible each day. I echo the thanks others have given for this list and RBT. On Wed, Feb 22, 2012 at 8:26 AM, Bill Downall <[email protected]> wrote: It's nice to see Professor Wills here! You know a topic like this would get him going. Bill, in my mind, a basic reason to normalize fully is to create a database that is least likely to need either schema changes or awkward exception-handling down the road. If you do not normalize, and you provide room for 3 phone numbers, some day you will have to put the fourth phone number in the comments, or change the schema to allow for 4 phone numbers. Schema changes are expensive, because all forms and reports and procedures and eeps and views and rules and triggers and applications that relate to that data may have to be changed, too, and cannot be done by users through "settings", but have to be done by programmers. Putting the data in the "wrong" place like the comments means people won't find that data with a normal search or query. There are other good reasons to normalize, like not "wasting" columns that are usually blank, and not having to search three or five columns instead of one (For example, to determine what customer might have sent us an incomplete or garbled fax message or credit card transaction where all we know is that their address is "345 Main Street"). But avoiding future expensive schema changes is the main one. Bill On Wed, Feb 22, 2012 at 11:02 AM, Wills, Steve <[email protected]> wrote: "Too relational" is a state that is rarely achieved, IMHO. I think your issue/question often and I like the direction of your thinking. I guess that thinking about such makes me a little "twisted" to some. I also own my own barcode-scanner - well enough about my predilections! -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
