The example you give is a form of hungarian notation, wherein an abbreviation representing the type of the variable is the first part of the variable name. Hungarian notation is not generally considered good practice for a variety of reasons; it is usually unnecessary, it interferes with the readability of the code, and since there is no actual connection between the variable name and the type, thus there is no guarantee that the notation will be correct. (For example, if you declare a variable as one type in a C++ program and then later change the declaration, the type given in the variable name will then be incorrect. This happens a lot in practice.)

Table names, column names, and database names all exist in distinct namespaces in MySQL. Thus, if you give a table name "Users" there is no chance of ambiguity if you also name a column in that or another table "users" so there is no advantage to be had by including "tbl" in the name.

I like to name tables and columns so as to make the queries read most like plain English. Thus, for example, I might name a table "Users" which would contain columns for "username," "address," "telephone," etc.

I got into a long argument with a good programmer I know about whether or not to name tables in the singular or plural, e.g. "Users" or "User." I was arguing for the plural and he insisted you should never name a table in the plural. I don't think it really makes much difference.


Here is an explanation of how I like to name tables and columns. I'd like to hear what other people think of this.

I don't like to include the table name in the column name. Thus, in the "Users" table you might have columns named "Address," "City," and "State" but it isn't good practice to name these columns "Users_Address," "Users_City" etc. If you do it this way your queries will look like "SELECT Name, Address, City FROM Users" or "SELECT Users.Name, Users.Address FROM ..." Compare that to "SELECT Users.User_Name, Users.User_City FROM ..."

For tables which exist just to represent many-to-many relationships I like to name the tables with the names of the tables which are related joined by an underscore. For example, if I have a table "Users" and another table "Permissions" (storing perhaps different things a user can do, like "insert into accounts" or "update transactions") then the table showing which users have which permissions I would name "Users_Permissions."

There is a good argument to be made for including the table name in the id field, such as "Users.User_ID" instead of "Users.ID." If the key columns are the same you can do a join with simpler syntax: SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions USING (User_ID) LEFT JOIN Permissions USING (Permission_ID);


However, I still prefer to name primary key id columns just "id" and then name the corresponding foreign key columns in related tables after the singular form of the names of tables they are related to. E.g.:

mysql> create table Users (id int(6) unsigned primary key, name varchar(32));
Query OK, 0 rows affected (0.08 sec)

mysql> create table Permissions (id int(6) unsigned primary key, name varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> create table users_permissions (user int(6) unsigned default NULL, user int(6) default NULL, UNIQUE KEY x (user, permission))

SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions ON Users.id=user_permissions.user LEFT JOIN Permissions on permissions.id=user_permissions.permission;

If you've never read Donald Knuth going on about Literate Programming you might check this out: http://www-cs-faculty.stanford.edu/~uno/ lp.html

Just like with parenthesis styles, you can name database objects whatever you want and it will still work. But good nomenclature makes it all so much more clear.



Douglas Sims
[EMAIL PROTECTED]



On Aug 11, 2006, at 4:08 AM, Barry wrote:

Hello everyone!

I am looking for a standard naming convention for databases.

For example: is it good to use tablenames in column names like:
table => tb_id,tb_text,tb_name

and such.

Probably there is some kind of overall naming convention out there, looked on google and such but only found conventions that people personally liked but no standards.

Thanks for any help :)

Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to