John W. Colby
jwcolby at colbyconsulting.com
Wed Jun 22 11:56:23 CDT 2005
Good job Joe, my point exactly. I have seen (and in fact am currently working on) too many tables where the PK is just called PK or ID or something silly like that. In the case I am working on are named something real (TherapyLocationID) but that same field name is used in several different tables, both as the PK and the FK in the child tables. The problem becomes when you try to search on "where is this field used", without a table prefix in the field name, you get hits from every table where that field name is used. I currently am trying to straighten out a mess where one table has the field TherapyLocationID as a FK. And it needs to be removed entirely. The therapy location is NOT one to one, thus there is now a table that says where the therapy occurs, but in order to find it... Well... Let's just say there about 100 hits only about 20 of which have anything to do with the field in the table I need to modify. I have to search through all 100 of these forms, reports, modules etc to visually look at which table that object is referencing when it uses TherapyLocationID. Can you say PITA? If all tables had a table prefix, this would not be an issue. I personally use the first two characters of the table name if the name is a single word: Tbl child = CH_ID CH_Lname CH_Fname Etc tblPeople = PE_ID PE_Lname PE_Fname Etc. I use the first letter of each word if the name consists of more than one word. tblInvoiceLineItem - ILI_ID ILI_IDIN (the FK holding the PK of the INvoice table Doing this ensures that if I search for PE_ID, I will get ONLY hits from the PEople table. If I search for any field with ILI_XXXX I will get only hits for items referencing the InvoiceLineItem table. Notice that I then name all FKS with YY_IDXXXX ILI_IDIN for example In essence I take the PK name (IN_ID), remove the underscore and reverse the ID and name part (IDIN) and place it immediately after the underscore (ILI_IDIN). Why do I do this? Because I push all IDs to the top of the table and then all FKs look like YY_IDAAA YY_IDBB YY_IDCCCC YY_IDEEE Etc. ALL fields with _IDXXXX are an FK. Just some things to think about. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht Sent: Wednesday, June 22, 2005 12:37 PM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Field Naming ( Just once I want to be ahead of JC ) When naming fields in table design view I try to prefix the field with something to identify the source table i.e.: Custfname custLname repfname replname Joe Hecht Los Angeles CA -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com