Jurgen Welz
jwelz at hotmail.com
Thu Feb 22 11:17:31 CST 2007
William: I ran such a design for a law office database and what killed performance was the sheer number of records in something like a phone number table. If for example, we stored the home phone number in an employee record, pulling the phone number from a 20 employee record table was trivial. However, culling the number from a table that includes those numbers together with all the company, client and other records that included fax, pager, cell, alternate address numbers etc, well now the table has 20,000 records. Yes the phone numbers were stored in a sub form, but the usual use involved looking up an entity with the purpose of contacting them. I truly appreciated the functionality of the design. If I entered a phone number that existed, I immediately knew something about the relationship implied between the current entitiy and the entity that shared the phone number. And changing the number where someone else had the same number implied additional information, though in fact, the numbers were never changed, but just deactivated and new numbers added. Very important in proving notice and communications in a law office environment. Bottom line for me was that displaying a record took several times as long as a flatter data design. I am afraid that applying the structure in my current environment will result in a significant performance hit and this is the primary issue I've been asked to address. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "William Hindman" <wdhindman at dejpolsystems.com> > >Jurgen > >"As much as I'd love to have a table of phone numbers, with a table >of number types, and addresses and address types, and relate them to a >single table of entities of both corporate and people types, in all their >permutations and combinations, this has proved to be far slower than having >a separate table of corporate and individual entities and just keeping >phone >numbers and addresses with them." > >...this is the template I moved to more than two years ago ...what I've >found is that the address info and contact means are not required for most >processes and fully normalizing them with fk to a super entity ID allows me >to call them only when required rather than every time a contact or company >record is called ...I display them only in JIT subforms when needed ...and >that makes the critical data records much shorter and thus quicker to >process ...while some processes take more time to accomplish, the overall >application performance is at least as good as the prior approach ...the >overall size of the first database I did this with dropped considerably >because of the "full" data normalization ...ie, instead of having >Loxahatchee, Fl, USA, 33470, lat, long repeated in numerous company and >contact records it now exists only once in the db. > >...one of the drivers behind adopting this approach was the ease it >provides >in deterring user data entry errors and thus the money it saves in >marketing >campaigns ...the returned mail has dropped drastically. > >...I don't pretend to have your expertise in Access but based upon my own >experience with that data model, I can't say that my results match what you >posited as a far slower performance. > >William Hindman _________________________________________________________________ Free Alerts : Be smart - let your information find you ! http://alerts.live.com/Alerts/Default.aspx