I share most of these conventions, and I think the key is consistency. Consistency makes the database usable, without guessing or having to refer back to notes or other uses of the table or field.
My golden rules are 1. Key names are unique within the database this avoids any confusion when joining tables. I have one cross-reference table Model_ID int(11) NOT NULL default '0', Model_type_code char(1) default NULL, Country_code char(2) default NULL, Manufacturer_code char(2) default NULL, Range_code char(2) default NULL, Derivative_code char(2) default NULL, Trim_code char(2) default NULL, Engine_code char(2) default NULL, Fuel_system_code char(2) default NULL, Body_type_code char(2) default NULL, Body_doors char(2) default NULL, Transmission_code char(2) default NULL, Intro_date int(11) default NULL, Term_date int(11) default NULL, Modified_when date default NULL, PRIMARY KEY (Model_ID) where almost all the fields are primary keys of other tables. Using 'id' as the primary key for all tables (or duplicating key field names at all) would be a nightmare when you are trying to understand how tables relate to each other if you are working with large numbers of related tables or cross referencing tables. 2. Sequential auto generated ids get 'ID' at the end e.g. 'UserID' I use this as a marker and avoid using them directly to avoid users spoofing them ( update_user.php3?UserID=1&Privilage_levelID=2 opens a security risk ) 3. I capitalise first letters of field names. This differentiates fieldnames from variables in php (where I always use lowercase ) which I find makes coding more readable and reduces the risk of errors . 4. I think 'Current_manufacturer_code' is more readable than 'CurrentManufacturerCode' so I prefer _ 5. Use descriptive fieldnames 'Current_manufacturer_code' is more typing, but 'c_man_cd' is meaningless to somebody else or six months later when you come to rework some coding. I also like my SQL to be as close to natural language as possible, for the same reason, and this helps. In the end consistency is essential, much of the rest is personal preference. Peter > -----Original Message----- > From: ryc [mailto:[EMAIL PROTECTED]] > Sent: 20 October 2001 19:48 > To: Mike E; [EMAIL PROTECTED] > Subject: Re: Field Naming Standards > > > I am not aware of any document that describes naming conventions, but > through my use of databases and design I have found a scheme that > works for > me. I follow these rules: > > 1. The autoincrement field (or unique id) for a table is named "tablename" > ++ "id". So if the table is "books" the unique identifier for the > row would > be "bookid". > 2. When an attribute in one table is the same as in another table > (ie in the > table "books" I might want to reference a row in the "author" > table in which > case I would name the attribute in the books table "authorid"). > 3. I use lowercase names for both tables and attributes.. The > reason is I am > not a big fan of uppcase letters unless they serve a purpose > (like in OO the > class name would be upper case while methods on the class are > lowercase). In > SQL there isnt much of a need to diferentiate between a table and a > attribute because you can determine easily what the name is from > the context > in which it was used in the query. But I think this is a > preference thing, I > could see someone thinking its very useful. > 4. When naming an attribute I try to reflect the meaning of the > data as much > as possible in the attribute name. Because SQL doesnt let you > easily convey > to the client what the field means, you must do it in the > attribute name. So > instead of using something like "aptno" for a field I would choose > "apartmentnumber". > 5. If you use "apartmentnumber" in one table, make sure to call other > references to it "apartmentnumber" as well... Dont change it to "aptno". > This follows from rule 2, I just thought I would say it again in another > way. :P The same goes for code that uses these attributes. If a variable > holds the value from the "apartmentnumber" attribute, dont call > it "aptno". > It might be clear in this example where that data came from but > that may not > hold for other examples... and definitly if someone is going over > your code > who is not familiar with the database it will help (or if you > havent looked > at the code in a really long time). > > That is about it for now... anyone care to add? > > ryan > > > I've been developing in MySQL for some time now, but as I go along I've > > noticed that my naming conventions have changed dramatically, and I was > > wondering if there is like a set of guidelines or something I could take > > a look at. A set of standards, if you will, for naming conventions of > > fields in tables in MySQL databases. > > > > For example, using all lowercase for field names, separating words with > > underscores, or not, and whether to use a descriptive auto_increment id > > (such as userid, newsid) or if to just use "id" for every table. > > > > Any discussion on this is much appreciated. > > > > Mike > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
