OdysseyApplications3Database < CF

CF Web>Software>WebCapabilities>OdysseyApplications>OdysseyApplications3>OdysseyApplications3Database (2008-11-12, IsaacMorland) (raw view)

EditAttach

---+ 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.

-- Main.IsaacMorland - 05 Nov 2008

Topic revision: r2 - 2008-11-12 - IsaacMorland

Information in this area is meant for use by CSCF staff and is not official documentation, but anybody who is interested is welcome to use it if they find it useful.

Other Webs

My links
- People
- CERAS
- WatForm
- Tetherless lab
- Ubuntu Main.HowTo
- eDocs
- RGG NE notes
- RGG
- CS infrastructure
- Grad images

Edit