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
Doug@stripped
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/mysql?unsub=1
