Naming conventions for MYSQL

Databases
1

Hi,

I’m wondering how you name your fields within a table. I’ve tried googling but there doesn’t seem to be one standard way to do so.

For example, in my member table, I have fields ID, forename and surname.

Should I name them:

  • member_id
  • member_forename
  • member_surname

---------or

  • memberID
  • memberForename
  • memberSurname

---------or

  • ID
  • forname
  • surname

Thank you!

2

the last alternative is far better

embedding the table name as part of the column name is counter-productive and needless noise

3

Thanks for your reply. What happens though if I have another table named ‘parent’ and within that table I have a field also named ‘forename’?

How would I distinguish between the two “forename” fields in the two different tables? Thanks!

4

this would only occur if you happened to reference both tables in the same query

in that case you would use table column qualifiers, as well as column aliases…

SELECT members.forename AS member_forename
     , parents.forename AS parent_forename
     ...
5

Thank you very much for your help. Appreciate it!

Just one more question, if a field is called “date of birth” for example (has more than 1 word) would I write “dateOfBirth” or “date_of_birth”? Thanks again.

6

dateOfBirth or dateofbirth or dob (less chance of a typo in the last one)
An underscore can sometimes be hard to spot in a hurry.

The only time I add anything to identify a field as from a particular table is when using id.
As there are usually several tables with a field called id, I tend to add a single letter in front of id. eg mID for members table, fID for the flight table, aID for the accounts table. I find this helps me when including a foreign key, as I can tell which table it is related to; months later this is a real benefit, imho.

7

birthdate :slight_smile:

8

haha - good one! Thanks Rudy! :slight_smile:

Thanks Dr John. Yes, I was curious about what people do for id fields.

Thanks a lot! Much appreciated.

----By the way, one more question, if I have a table named member type should I name it with an underscore e.g. member_type or memberType or membertype?

Thanks!

9

membertypes

all table names should be plural, in my opinion, to constantly reinforce the idea that they are sets of rows

10

I read somewhere online somebody was encouraging to use singular, not plural. :confused: What shall I do?!

Ok, just found this article: http://leshazlewood.com/software-engineering/sql-style-guide/

and they also suggest to use plural. Thanks! Is underscore between 2 words okay for tables as it looks easier to read? e.g. member_type as opposed to membertype?

11

i don’t particularly like his use of “_maps” for relationship tables, but that’s a minor peccadillo

it’s fine with me, i do the same