database columns and foreign key normalization

Question

i am building a new system (lamp - ubuntu server, php 5.3, mysql 5.5, apache 2, redis) for my company (like a version 2.0)

i want to make it right and make it better.

i want to use innodb and i want to know if there's a standard in naming columns and foreign keys.

for example:

table_a
 table_a_id
 table_a_name
 etc...

this way when you left join its very easy to distinguish but if you have long name it can be painful to write sql.

table_a
 id
 name
 etc...

this way, the table is very easy to understand but you have to use the alias or table name every time you left join.

is naming a database.table.column part of the database normalization?

Answer 1

the naming convention of database, tables, columns, keys, foreign keys has nothing to do with the database normalization.

although, i recommend to keep the same naming convention structure everywhere. all my names are pretty standard, single word or if it's a phrase it's separated by underscore, no numbers nor special chars. same for columns, keys and fk (with exception with number) and i always use plurials (its rare you only have 1 db, 1 table with 1 column and 1 row)

here's what i do:

databases: companies
tables: clients, clients_products, products
column: id, name, address, city
key: name
unique key: name

this way when you query your data, it's very clear what you request or what you look for.