Home Sitemap SEO PHP

database design : naming conventions

Naming conventions are a great way to keep your code well organised, well structured, less prone to bugs,and simpler

This database naming convention is our personal preference . . .

As example, imagine a database of customers; the orders each has placed; and the items in each order

c_customer
c_id int autonumber
c_forename varchar(30)
c_surname varchar(30)
c_telephone varchar(50)
... etc

o_order
o_id int autonumber
o_c_customer_id int
o_py_payment_type_id int
o_timestamp datetime
... etc

ol_orderline
ol_id int autonumber
ol_o_order_id int
ol_p_product_id int
ol_product_name varchar(50)
. . . etc

what are the rules ?

  1. always use lower case, seperating_words_with_underscore
  2. prefix each table and field with a letter (or two) specific to the table
  3. keep table names singular (eg 'o_order' not 'o_orders')
  4. always have a primary id, autonumbered, by the name of 'prefix'_id
  5. name foreign keys 'prefix'_'foreigntablename'_id (in example above o_id=ol_o_order_id)
  6. keep foreign keys bunched together under the primary key
  7. never mix eg: 'forename' and 'first_name'. set yourself a standard
  8. always use the same size varchars for same data eg 30 chars for forenames
  9. use a datetime even if you are only going to be using the date part
  10. generally, use $camelHumpStyle variables in your php and keep $c_id style for database fields
  11. use the same field name in forms too. eg: <input type='text' name='c_forename' maxlength='30' />

what are the advantages ?

  1. all field names are unique, so you can do without 'AS'. eg:
    SELECT customer.id AS custid, 'order'.id AS orderid FROM ....
    becomes
    SELECT c_id, o_id FROM ....
  2. fields are ok format for php variables, so can be extracted without fuss
  3. lowercase underscore makes the fields an ok format to generate <xml_tags> from
  4. you'll always remember primary key and foreign key fieldnames (if you can remember o is for order etc)
  5. you'll know, for example, $orderID is internal to your program and $o_id came from the database
  6. you are now free to write code that can work out by itself how the tables relate

not convinced ?

Sounds too simplistic to be worthwhile doesn't it ?

We reckon clever code is simple code; and simple code comes from simple practices like this.