Shamil Salakhetdinov
shamil at users.mns.ru
Sat May 12 10:12:37 CDT 2007
John, John, The "hardcore" core table of your database seems to be the BeneCheck table. All the rest are "in between" sharing lookup and production tables' roles depending on context in which they are used. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, May 12, 2007 6:51 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Lookup and productiontables'alternativesymbolickeys... In this case there are many core tables. These are insurance policies. Policies are added every time a claim is processed where that policy does not exist. If a policy is added, a policy holder is added. The claimant is added (the person filing the claim). The claim is added. Then monthly (or weekly) the benefit record is added. The benefit record is a record which holds data about the benefit owed THAT pay period. These change over time as the number of days, the amounts removed for taxes etc. The benefit check info is a table that holds data about the check about to be issued for that specific benefit record. More than one check can be cut - one to the claimant, another to his divorced spouse, one for child support etc. Then checks are cut and referenced back to the benecheckinfo record that caused that check to be cut. So you can see that literally daily, new claims are processed. USUALLY these are for existing policies - the policies USUALLY are held by a COMPANY providing disability insurance, but not always, sometimes individuals buy policies. So pretty much ALL of the tables from policy holder and claimant on down are modified on a daily basis. 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 10:33 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Lookup and productiontables'alternativesymbolickeys... John, My experience shows that within a database (developed for small/middle size businesses) there are usually a few *core* production tables, which are getting updated (rows inserted, updated, deleted (marked as deleted)) often. All the other tables are getting updated not that often and they are mainly used as *lookup* tables for the few *core* production tables. IOW here lookup is meant in a broad sense. -- 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:58 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Lookup and production tables'alternativesymbolickeys... Shamil, I consider a lookup table to be small simple tables such as state, color, product type etc. Definitions like this are definitely gray areas, and if you apply the criteria "how often are they change" then I see where the chains would be much shorter. In the call center database, the "changes" would start at the claimant / claim / benefit / BeneCheckInfo / Check. There are cases where the same claimant has multiple claims, but MOST of the time you start with a new claimant. That often reaches back up to the policy holder / policy portion of the chain if that policy holder has never been entered before, which is less common than a new claimant. Given that I have a framework 8-) I can (and am able to) track all form usage, so I could do some statistics as to which forms are used how often. I just went and checked and I have the object tracking turned off so I would have to turn it on and wait a few weeks to get meaningful results. 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 6:38 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Lookup and production tables' alternativesymbolickeys... Hello Stuart, Thank you for your sample: this looks for me more as "lookup up chain" - a kind of 6 levels classification system for a Voter. I obviously missed to mention such cases in my first posting. IOW: Province->District-> Local Level Government Area->Ward->Locality->Location is in my opinion a lookup tables' chain Area->Ward->Locality->not a master->detail->...->detail chain of production tables. Of course all depends on point of view: I'd distinguish lookup and production tables based on probability of how often their data is getting changed... BTW, do you have autonumber/identity keys for all your tables? do you also have (alternative) symbolic keys for your tables? Thank you. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Saturday, May 12, 2007 2:14 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Lookup and production tables' alternative symbolickeys... On 12 May 2007 at 13:52, Shamil Salakhetdinov wrote: > 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? I'm currently working on an Electoral roll which has a seven level chain: Province District Local Level Government Area Ward Locality Location Voter -- Stuart -- 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 -- 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com