mysql naming convention

Question

I have generally always used some sort of Hungarian Notation for my field names in my tables e.g.

#Table Users
u_id, u_name, u_email etc...

#Posts
p_id, p_u_id, p_title, p_content etc...

But I have recently been told that this isn't best practice. Is there a more standard way of doing this? I haven't really liked just using the field id as this is then requirs you to select table.field for fields names that appear in mutliple tables when using joins etc.

Your thoughts on what is best practice would be appreciated.

Answer 1

Depends on your working style. There are no fixed set of rules.

I use camelcase tableNameFieldName for e.g catID, catName, catDesc

Answer 2

The best practice is the most comfortable for you or your team. Every programmer has it's own opinion about using notations.

Answer 3

This seems like one of those questions where you'll get numerous answers, all of which are "right." Different teams and environments will prefer different conventions. I even find that my own practice will vary from project to project a little. The only suggestions I could offer are

1. be consistent within projects,
2. don't be overly-verbose,
3. and don't use reserved words

Just my two cents. Wiki'd.

Answer 4

I personally use lowercase for table names and pluralize. Tables such as 'people' with person records. But I got that from Rails and adopted it after time. Love it though, and this made my databases a whole lot readable. Also using so many underscores will make your databases less readable. Also I divide my database names using _ for development, test, production, alpha, beta, prototype. Once you worked with 100,000 tables or so, you'll stop writing names with too many underscores :)