Scott Penrose

Schema

Scott is an expert software developer with over 30 years experience, specialising in education, automation and remote data.

In a recent chat with Dr-Chuck database schema design came up, and the style used in Rails. And I mentioned it is a fairly standard style, but then I found it difficult to find references on the net. So here is my rules...

Rules?

These rules are good:

  • Any naming standard is better than no standard.
  • There is no "one true" standard, we all have our preferences
  • If there is standard already in place, use it. Don't create another standard or muddy the existing standards.

Schema

Simple Rules

  • Lower case all table and field names
    • Use "_" if you need, e.g. name_first
  • Field names, grouped to the left (most to least significant)
    • name_first, and name_last, never first_name, last_name
    • works for references too - table_field
  • Primary key always "id" (not table_id)
  • Reference field = table_id - e.g. "user_id" in group table
  • Table names not plural - When you write user.name it is logical. users.name is not.
    • In OO, you may have Users and User as class names, but it is only one table so just user
    • Save lots of confusion - users to user may be easy, but what about people to person
      • if people, is the remote id person_id - fail !
    • However - many of the standards suggest plural, so this is my least enforced)
  • Don't use unnecessary primary keys - e.g. Join table = group_id, user_id (does not need id)

The Bad and the Ugly

  • Table prefixes - especially name prefix for forms of data separation
    • Use a different schema
    • or in some DB, a different user (e.g. Drizzle supports this)
    • More secure, easier to read, does not change SQL just from configuration !!!

Special Cases

SQL

When writing SQL we also have rules.

Simple rules

  • SQL keywords all upper case - "SELECT id FROM user WHERE name LIKE '%Scott%'
  • Layout - long examples should be indented.

Coding style

  • Use bind columns - (almost) always
    • Perl DBI = ???
    • Python DBO = ???
    • .NET, Java, etc.
  • Throw exceptions
    • This is more of a general rule anyway, not just DB
    • DBI - set on connect RaiseExceptions => 1 - very very handy

Perl

Specifically I have a perl example: You can put this in a variable or directly.

#!Perl
my $sth = $dbh->prepare(q{
  SELECT
    id, name
  FROM
    user
  WHERE
   email LIKE ?
});
$sth->execute('%@dd.com.au');

See Also

  • Database
  • Coding