Time Tracker Database Schema

I'm working on a new project that was intended as a large scale demo for the flex2rails library but has grown into it's own beast. This article discusses the thinking behind the database design for time tracker.

The image to the right is the full schema for time tracker as of version 2007.11.29. I'll start with the basic business requirements to explain the entities.

Business Requirements:

• register users that belong to a company
• register companies (customers) that have contacts
• associate mailing address, email, phone, sms with contacts
• support projects that have discrete tasks
• associate users with projects
  
• support time sheet entries made by users
• support ad-hoc user attributes such as skill level, job title, etc.

And here are my ground rules for creating entities in rails projects:

• all tables have a discrete, autogenerated ID column as primary key
• all tables include lock_version, created_at, updated_at columns (exceptions are for :through associations)
• all tables include a bit_flags column that contains multiple boolean flags held in a single integer. the mappings are defined in the rails model using the has_flags plugin
  
• table names are plural
• most columns are not-null
  

There are two basic entity groups: user, contact and company related and project related. Lets start with the users.

Users: The users table is configured for open ID. it contains and open id URI, and email and optional name. There is also an access_key column that will be used to continue login sessions across multiple applications, similar to a session id, but more flexible. Bit flags, defined in the model, include active, admin, banned, etc. An associated table is user_attributes. This is used to store ad-hoc values such as job title, rolls, etc.

You will notice that there is no password or password_hash column. We use open id so don't require storing passwords in the user table. But, if we did store passwords, the last place to do it is right along side the access/user code, or even in the same database. So, if we ever have a need to actually store passwords, it would be in a separate database similar to a unix shadow file.

Contacts: Entries into to contacts table may or may not be associated with users, typically they are. Examples include project members, coders, etc. or customer contacts, managers, etc. But, some customers are not registered users so they are not associated with the user table. Other contacts may be virtual, such as "bill to" or "ship to".

Contact methods define ways to communicate with a contact such as phone numbers, email addresses, sms, chat handles, etc. Contacts may also be associated with zero or more mailing addresses that include physical coordinates.

Companies: Company rows may exist on their own but more typically they are associated with (and owners of) projects, have contacts, addresses, etc. Each project must be associated with a company, typically the client/customer.

Projects: Projects are associated with a companies and can have project tasks and project members. Projects can also themselves be associated with other projects as a sub-project. Project members are registered users. Projects typically have a start date.

Project Tasks: Project tasks belong to projects and users and have a single task type. Tasks have a start date and due date and optionally number of hours bid.

Time Sheets: Time sheet entries are created by users to log time spent on project tasks. Logged time includes a start time and number of hours. There is also a single activity type associated with each time sheet. Activity types are user defined and typically include design, coding, debugging, testing, etc.

So this is a quick tour of the Time Tracker database schema. The complete migration scripts are contained in the open source project and will be made available for download when the project goes to beta (projected for Q1-2008).