Shamil Salakhetdinov
shamil at users.mns.ru
Thu Mar 2 16:37:18 CST 2006
John, I just noted that your naming conventions are solid and systematic and based on LRNC - and that's "what doctor ordered". <<< I adapt where possible. >>> Either do I. Shamil ----- Original Message ----- From: "John Colby" <jwcolby at ColbyConsulting.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Friday, March 03, 2006 12:13 AM Subject: Re: [AccessD] Table naming conventions >I started with LRNS, and still use it for variables etc. I do not claim to > be the originator of anything, I adapt where possible. > > > John W. Colby > www.ColbyConsulting.com > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil > Salakhetdinov > Sent: Thursday, March 02, 2006 8:11 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Table naming conventions > > John, > > AFAIS your naming conventions are based on LRNC (see e.g. Stan Leszinsky > "MS > Access 97 Expert Solutions"). > One exception are field names - the recommended in LRNC way is to not use > underscore and to use 3-4 chars standard abbreviations to prefix field > names: > > - Company - comp > - Person - pers > - Employee - empl or emp > ... > > I do use LRNC for the last 10 years. It worked very well in many > situations. > > Best regards, > Shamil > -- > Web: http://smsconsulting.spb.ru/shamil_s > > ----- Original Message ----- > From: "John Colby" <jwcolby at ColbyConsulting.com> > To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> > Sent: Thursday, March 02, 2006 2:12 PM > Subject: [AccessD] Table naming conventions > > >> Folks, >> >> There has been a bit of discussion in the past few days re naming >> conventions as applied to tables. I have used conventions for many years >> and I sat down tonight and started documenting the conventions I use. I >> have shared my conventions in the past and will do so again here. I do >> these things because they work for me, in my environment. >> >> I have built a framework, which I use in any project where it is allowed. >> Having conventions allows me to make assumptions about how things will be >> handled, and write code that used those assumptions to make decisions. >> Even >> if you do not use a framework, having objects defined in a consistent >> manner >> can just generally make life easier. >> >> Feel free to discuss the methods and assumptions that I make, add how you >> do >> things (and WHY) etc. Don't bother calling the things I do stupid, if >> they >> don't work for you, don't use them. Share what does work for you, and >> WHY >> you do what you do. If you do something exactly opposite of the way I do >> it, explain why you prefer to do it that way. I find rules useless. I >> find >> reasons very useful. >> >> Table Definition Specification >> Tables are defined in a consistent manner so that they can be recognized >> and >> worked with easily. All of the definitions that I use have been >> assimilated >> from other people's ideas or developed over time, and all such >> conventions >> exist to solve a problem, whether that be readability, distinguishing >> functionality or allowing the framework to make assumptions. >> >> Table definitions consist of several parts. >> 1. Tables always use an autonumber PK, whether it appears to need one >> or not. This is done for a variety of reasons, ranging from divorcing >> the >> "table link" (PK/FK)from the unique index and making it trivial to change >> that unique index without changing the "table link", to the consistency >> of >> PK data type which allows specific assumptions in the framework. The PK >> is >> always a long integer, and a single field, which easily allows searches >> and >> other programmatic manipulations that wouldn't be possible or would be >> tedious if the PK was a multi-field set. >> 2. Table naming conventions. Tables are prefixed with 'tbl' so that as >> table names are encountered in forms, reports and queries they can be >> distinguished from other objects such as queries. Thus tblPerson, >> tblCompany, tblVehicle. >> 3. I have used other table prefixes to cause tables to group by type >> and be visually distinguishable from each other. Tbl for main tables, >> tlkp >> for lookup tables, tmm for many to many tables, ttmp for temporary tables >> etc. There is a lot of discussion about whether grouping by type is >> preferable to grouping by functionality, i.e. all accounting tables group >> together, all shipping tables group together etc. That is very much a >> 'preference' kind of thing. I have done both, even within the same db. >> It >> is possible to throw a "type" suffix on the end of the tables separated >> by >> an underscore if you care to group by function but be able to distinguish >> table type. tblPeopleVehicle_MM for a many to many, tblState_LKP for a >> lookup. >> 4. Field naming conventions. Field naming conventions have several >> parts. In general field and table names should only change in the most >> dire >> circumstances. If any object name changes, I have to do a search and >> replace to find and fix them. If there are a bunch of Fes working >> against > >> a >> single BE, it becomes a nightmare. >> a. Data type prefixes are never used in field names because of issues >> with data type changes. There are just too many data types, and they are >> often changed well into a project, for very valid reasons. >> b. A prefix to the field name is created using the first two characters >> the table name where the table name is a single word - PEople, VEhicle, >> COmpany. In cases where the table name is multiple words, the first >> character of each word is used. InvoiceLineItem ILI, PeopleVehicle PV, >> CompanyProperty CP. An underscore is used to distinguish the field name >> prefix from the rest of the field name. PE_ID, PE_FName, PE_SSN. Use of >> the first two characters if each word is acceptable to avoid collisions >> and >> make the field prefix more readable. PEopleVEhicle PEVE_ID. >> >> Obviously as a project gets large a table is required to track field name >> prefixes already used, but for small to medium sized projects this allows >> the developer to rapidly recognize which table a field comes from just >> from >> the field name. If this naming convention is rigorously followed then >> every >> field in the db has a unique name, which allows "search and replace" type >> of >> operations to be performed without manual intervention. >> c. IDs (PK/FKs) are distinguished by the capitalized characters 'ID' >> in the field name. >> d. PKs use the convention of Prefix characters, followed by an >> underscore followed by ID. PE_ID, VE_ID, CO_ID. >> e. FKs use the convention of Prefix characters, followed by an >> underscore, followed by ID followed by the prefix characters of the >> foreign >> table. PV_IDPE, PV_IDVE. CP_IDCO. By placing ID immediately following >> the >> underscore, PKs and FKs can be immediately distinguished from other types >> of >> fields. >> f. PKs and FKs are grouped at the top of the field list in design view. >> This is done so that the PK and all FKs can be seen at a glance without >> having to search through the fields looking for PKs/FKs. >> >> >> Thus field names look like: >> tblVehicle: >> VE_ID >> VE_Color >> VE_Make >> >> tblPeople >> PE_ID >> PE_LName >> PE_FName >> PE_SSN >> >> tblPeopleVehicle >> PV_ID >> PV_IDPE >> PV_IDVE >> PV_DateAssigned >> >> As you can see, by using the _ character it is easy to distinguish the >> prefix from the rest of the name. By using the ID immediately after the >> underscore it is easy to find every PK/FK field. By grouping all the >> PKs/FKs at the top of the table, no searching is required to find the >> PKs/FKs. By using the _IDXXXX construct, it is easy to visually look at >> the >> FK and just "see" what table that is the PK in. >> >> As I said above, to me reasons are the important thing. They define why >> I >> do what I do and allow me to evaluate whether making a change to what I >> do >> is useful to me. Rules without the associated reason are useless since I >> cannot evaluate how it applies to what I am trying to do. >> >> Rules are made to be broken, but I want to know the danger of doing so. >> >> John W. Colby >> www.ColbyConsulting.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