jwcolby
jwcolby at colbyconsulting.com
Sat May 12 07:46:40 CDT 2007
Shamil, My Disability Insurance Call Center database has some long chains chains: Underwriter / Policy / Claim / Benefit / BenefitOffset Underwriter / Policy / Claim / Benefit / BeneCheckInfo / Check Policy Holder / Policy / Claim / Benefit / BeneCheckInfo / Check Claimant / Claim / Benefit / BeneCheckInfo / Check In such cases it is not uncommon to have to start at the top of the chain, i.e. "view all checks for policy holder", view all checks for the claimant, view all checks for the underwriter. I do not understand what you mean by "alternative symbolic keys". If you mean fields that would be a natural key FOR THAT TABLE (policy number in the claim table) I do not. I use ONLY surrogate keys, which are always long int autonumbers. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Saturday, May 12, 2007 5:52 AM To: 'Access-D' Subject: [AccessD] Lookup and production tables' alternative symbolic keys... Ho All, When you design highly normalized databases how often do you define alternative symbolic keys for your lookup and production tables? If you do not define such alternative symbolic keys what is your reasoning? FYI: I always define alternative symbolic keys for all lookup and for at least entry production tables. By "entry production tables" I mean first master (parent) table in master(parent)->detail->...->detail chains. BTW, what was the longest master->detail->...->detail chain for a database you developed? >From my experience an average value for the length of these chains is >2.5; if it's 3 then the third level table is usually a (many to many) relationship table; sometimes length of chains can be even 5 but I have never seen chains longer than five... Do they exist? Of course classification tables' chains could be very long but I consider them as an exception, which supports the "rules/hypothesis" mentioned above. And as far as I see from most of the real life samples when classification is used then master->detail->...detail chain is rarely longer than 3. And if it's longer then recursive structures are usually used... Please write about your opinion/experience on subject and related issues. Thank you. -- Shamil -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com