My two cents: I (sinfully) use the plurals in the table names. Can be confusing, yes, but if you are consistent one way or the other, you should be able to remember what you are doing.
My rules: 1) Each table name has a related table abbreviation (e.g., CLIENTS has CLI, PHONENUMBERS has PHONE). 2) Each column in a table contains the table abbreviation (e.g., CLI_Fname, CLI_Lname, CLI_HatSize). 3) The primary key of a table consists of "pk" followed by the table abbreviation (e.g., pkCLI, pkPHONE). 4) Foreign keys in a table consist of "fk" followed by the local table abbreviation followed by an underscore followed by "pk" followed by the foreign table abbreviation (e.g., fkCLI_pkPHONE is the foreign key referencing the primary key of the PHONE table). Note: there are two circumstances under which this wouldn't hold: (1) if your foreign key references something other than the primary key of the foreign table; and (2) if two foreign keys in your table reference the same primary key in another table (e.g., a table marked CALLS twice refers to pkUSR, once for the person who called and once for the person who answered: the notation can't be fkCALL_pkUSR for both, but is instead fkCALL_pkUSRFrom and fkCALL_pkUSRTo). 5) Junction tables use the two table names/abbreviations separated by an "x" (e.g., CLIENTSxLOCATIONS is the junction table between the CLIENTS and LOCATIONS tables, with a primary key of pkCLIxLOC, and--the longest and messiest within my system--foreign keys referring to CLIENTS and LOCATIONS: fkCLIxLOC_pkCLI and fkCLIxLOC_pkLOC). 6) If you have a table which lists the types of things in another table, follow it's name and abbreviation with the letters "ty". For example, in this example, the table listing the types of clients would be CLIENTSty, with an abbreviation of CLIty. CLIENTS then would likely have a column called fkCLI_pkCLIty that tells what kind of client it is. 7) When I am writing SQL, I strictly adhere to the cases as I have them written above. This makes a HUGE difference in readability. Indeed, much of my system is predicated on the fact that changing case can really stand out. "fkCLIxLOC_pkCLI" is extremely clear in meaning to me in large part because of this. FKCLIXLOC_PKCLI is not so clear. This may sound a bit complicated, but I find that the code is very easy to read. When I come back to a project I have not worked on in a long time, I can read the SQL without any trouble. HTH, Matthieu -----Original Message----- From: Andrew Dixon [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 7:30 AM To: CF-Talk Subject: OT: Field Naming Hi Everyone. I'm just starting a major project for a government client and the first job is to design the database. Is there any recongnised convention on the naming of database fields. In the past I have always named them [table_name_field_name] for example: projects_id where the table is called 'projects' and the field is called 'id'. Thanks in advanced. Best Regards Andrew. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181936 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
