Scott Penrose

Schema

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

Difference between revision 10 and current revision

No diff available.

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