Charlotte Foust
cfoust at infostatsystems.com
Tue Apr 24 11:07:57 CDT 2007
In modern databases, it's incoherent and unnecessary. In the databases of 25 years ago, where every table was a separate file, it made a bit more sesne to reduce the number of tables where possible. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Tuesday, April 24, 2007 8:55 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] OT a little - Code Tables For those who care about theory, this model is a definite no-no. A table (relation) is a collection of like entities, and since there is no observable penalty for the number of tables (unless it goes into the thousands), then IMO it is asinine, not to mention theoretically unsound, to use one table for several purposes. Assuming that you choose the one-table route, then you must create views or UDFs to isolate the collection of interest. Given the overhead involved in doing that (and the more rows, the more overhead), what do you gain by stuffing a bunch of unlike entities into a single table? The whole idea IMO is incoherent and self-defeating. My $.02. Arthur On 4/24/07, JWColby <jwcolby at colbyconsulting.com> wrote: > > >Have you been singing its praises lately, John? ;-} > > Only when someone is trying to use a single record table and adding > fields to the table every day. > > <grin> > > >In .Net, we generally stuff the info into a table in an xml file > >instead > of > in the database. > > I find it useful to have a copy (for my framework SysVars for example) > in the framework itself, then a copy in the FE. The FE copy is loaded > AFTER the FW copy so overwrites the FW copy if any changes are made in the FE. > This allows me to set up the framework to have default behaviors that > can be overwritten as desired for specific Fes. > > > 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 Charlotte > Foust > Sent: Tuesday, April 24, 2007 11:21 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] OT a little - Code Tables > > Have you been singing its praises lately, John? ;-} > > I use a very similar 2 field table for the same purposes in Access apps. > In .Net, we generally stuff the info into a table in an xml file > instead of in the database. > > Charlotte Foust > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby > Sent: Tuesday, April 24, 2007 8:13 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] OT a little - Code Tables > > Which little ditty is that? Are you referring to SysVars? > > > 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 John Bartow > Sent: Tuesday, April 24, 2007 10:39 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] OT a little - Code Tables > > Hi Ed, > My ponderings... > > I've seen codes like this before - in a USDA database. > > I've seen arguments on both sides as to its compliance with relational > theory. In any case IMO it is a confusing way to handle lookup values > as it > (obviously) contains more than one type of value. As long as you > retain the same programmer it probably doesn't matter - but who can do > that anymore? > So > all I can advise is to document, document, document! > > The one exception IMO for holding more than one type of value in a > lookup table is if the item will _never_ have more than one value - > then JC's little ditty works nice. In that case it is handled via code > not relational structure. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed > Sent: Tuesday, April 24, 2007 9:00 AM > To: dba-ot at databaseadvisors.com; Access Developers discussion and > problem solving > Subject: [AccessD] OT a little - Code Tables > > Hi All, > I'm not familiar enough with SQL Server but I have a question > regarding what I call Code Tables. I use them a lot when I develop an > application in Access, e.g., I'll have a table for counties i.e., > county code and county name or Providers, Provider code and Provider > Name. I have them as separate tables. I'm trying to make sense out > of the tables and relationships "my" > programmer created. He has one code table period! > Below is a look as to how it is setup. > > dbo_tblCodes > CodeType CodeId CodeName OrderOnForm > Ethnicity 1 Puerto Rican 1 > Ethnicity 2 Mexican 2 > Ethnicity 3 Cuban 3 > Ethnicity 4 Other Hispanic 4 > Ethnicity 5 Hispanic, Not Specified/Known 5 > Ethnicity 7 Not of Hispanic Origin 6 > Ethnicity 9 Don't Know/No Answer 7 > Gender 1 Male 10 > Gender 2 Female 20 > Health 1 Poor 5 > Health 2 Fair 10 > Health 3 Good 15 > Health 4 Very Good 20 > Health 5 Excellent 25 > Health 9 Don't Know/No Answer 80 > > This is just a little bit of the table but I think you can see his "logic" > here. Is this a common convention that developers use? Hate to see > what else I'm going to find as I try to wade through this. > TIA > Ed > > > Edward P. Tesiny > Assistant Director for Evaluation > Bureau of Evaluation and Practice Improvement New York State OASAS > 1450 Western Ave. > Albany, New York 12203-3526 > Phone: (518) 485-7189 > Fax: (518) 485-5769 > Email: EdTesiny at oasas.state.ny.us > > -- > 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com