Friday, November 30, 2007

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

Thursday, November 29, 2007

Non Intrusive Flex/Rails Remoting

Objective: create an open source remoting framework to communicate between Flex UI and Rails server without modifying any rails code.

Solution: Rails restful scaffold resource generation.

Flex has some great xml processing tools. Rails can easily generate xml out of the box. So, match up the 7 rails restful methods with a Flex/AS3 framework and life is easy...

This library includes connection configuration, index, show, create, update, destroy classes that operate against a remote Rails site. The rails code can be generated using resource scaffolding and remain unmodified.

Here is a live demo. It's a simple list/detail application that uses the remoting framework.

Download the swc here or the source code here. Or, you can check out the the latest by running:

svn co

Source code for the demo application is also available here.

Using the framework is quite easy. Simply create a controller and register the rails host and services in your main application. Then, create handlers for the discrete service methods including query (list), fetch (show), create, update and delete. Custom methods can be created by extending RailsService and implementing the send() method.

I'm currently updating the documentation with a step by step tutorial.

PureMVC - an Application Framework

I was introduced to PureMVC a few weeks ago through assertTrue's first rate video cast covering various Flex application frameworks including Cairngorm, MVCS, and others including my favorite, none of the above. But, PureMVC has many intriguing features, including...
  • great documentation--the best I've seen short of having to buy a bulky book from amazon
  • great use of standard patterns
  • good use of publish/subscribe event model for loose coupling
  • promoted as lightweight, but feels like a java framework
So as a java engineer it does feel lightweight, especially after working with spring, hibernate and j2EE--but after working with rails for the past two years, I still feel like it's a bit heavy. But for someone looking for the best framework for Flex applications, PureMVC gets my vote.

So why do I prefer none of the above? Well I'm not completely convinced, but I have a feeling that when a project gets to a usable size, the amount of registration for each command and value object would be overwelming. I also am more familiar with logic inside the controller as opposed to inside discrete mediators.