Charlotte Foust
cfoust at infostatsystems.com
Thu Mar 2 09:57:45 CST 2006
You use suffixes for ease of navigation. I prefer prefixes for grouping by types of objects. Different strokes. Charlotte -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Thursday, March 02, 2006 6:16 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Table naming conventions Not read all this, but one point I want to make is "why are people obsessed with PREFIXES?" I use the "_tbl" SUFFIX in my table names. Why? So that when I sort the list of tables I can use the initial letter of the table name to quickly jump to it using the keyboard. If they all begin with "tbl" you cannot do that. Simple. :-) Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: Thursday, March 02, 2006 6:12 AM To: 'Access Developers discussion and problem solving' 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