[AccessD] Lookup and production tables' alternative symbolickeys...

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




More information about the AccessD mailing list