Odyssey 3 Applications Database

All data are stored in the Postgres database at appdb.cs:5433. Each application has a schema.

Naming Conventions

Identifiers use all lowercase letters with underscores to separate words. This convention is chosen because it ensures that the identifiers as written in the schema definitions match exactly the identifiers used within the database. Postgres has case-sensitive identifiers, but interprets identifiers written in SQL commands in a subtle way: identifiers written alone are lower-cased as part of the parsing process; identifiers surrounded by double quotes are left alone.

Table Names

The first word of a table name identifies the portion of an application to which it relates. This is essentially a way of getting around the fact that Postgres has only a two-level namespace (within each database), where each table is identified by a schemaname and a tablename. In practice one additional level is normally enough.

Field Names

Field names should be fairly specific. Prefixing each field name with its table name is going too far, but unless the basic name of the field is reasonably unusual or unique a partial prefix is appropriate. For example, name is not a good field name (almost any table might need a name field), but committee_name would be fine (people_committee_name is too much).

Primary key fields should end in _id for numeric identifiers and _code for alphanumeric short codes. Key fields should begin with a reasonable short version of the table name, typically the part after the first word.

Foreign key fields should have the same name as the field to which they refer as long as doing so does not cause confusion. This facilitates using of NATURAL JOIN as much as possible. Primary key fields which are also foreign keys should definitely have the same name as the field to which they refer.

-- IsaacMorland - 05 Nov 2008