Database Tables / Naming Conventions

These are taken straight from Ruby. This means: -

• Table names are simply the plural state of the model class names
• Every table has an id field
  • it should be the primary key
  • it should be an auto_increment field
• Field names should all be in lower case.
• If a field links to another table, its name should be tablename_id
• If a field links to another table, it should be declared as an index

Take a look a the diagram below. It shows a sample table from a project. You will notice: -

• There is an id set as the primary key and setup as an auto increment field
• ajaxadverttypes_id is a link to another table
  • this field respresents the links between the object and the AjaxAdvertType object.
  • you can assume from the field name that there will be a table called ajaxadverttypes
• all the field names are in lowercase.

CREATE TABLE `ajaxadverts` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `subtitle` varchar(255) NOT NULL,
  `details` text NOT NULL,
  `image` varchar(255) NOT NULL,
  `bkgimage` varchar(255) NOT NULL,
  `buylink` varchar(255) NOT NULL,
  `infolink` varchar(255) NOT NULL,
  `ajaxadverttypes_id` int(11) NOT NULL,
  `cssname` varchar(255) NOT NULL,
  `usage` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `AJAXADVERTTYPE` (`ajaxadverttypes_id`)
) ENGINE=MyISAM  AUTO_INCREMENT=1;

Tags: database, database tables, naming conventions, tables