Shamil Salakhetdinov
shamil at users.mns.ru
Sat May 12 08:59:52 CDT 2007
John, Is the chain: Policy Holder / Policy / Claim / Benefit / BeneCheckInfo / Check Master->Detail->...->Detail chain? I'd think without knowing details of this business just based on the names of the tables - it's at least: Master (Policy Holder)->Detail(Policy)->Detail(Claim)->Detail(Benefit)->Detail(BeneCheckInf o)<-Master(Check) Again based on the names of the tables I'd expect the structure would be even more shallow - something like BeneCheckInfo being a relationship table for Policy, Claim, Benefit and Check tables i.e. the length of the chain is 3 not 6 (I can be wrong - I do not know this business). "Alternative symbolic keys" means for me an *additional* to surrogate (Autonumber/Identity/(long/whatevet bits)Integer/GUID/...) symbolic unique key. That's clear that for relationships (many to many) tables such keys if they exists are usually artificial, especially for long master->detail->... chains... -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, May 12, 2007 4:47 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Lookup and production tables' alternative symbolickeys... 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com