I’m trying to model a relationship in ActiveRecord and I think it’s a little beyond my skill level. Here’s the background.

This is a horse racing project and I’m trying to model a horses Connections over time.

Connections are defined as the Horse’s Current: Owner, Trainer and Jockey.

Over time, a horse’s connections can change for a lot of different reasons:

1. The owner sells the horse in a private sale
2. The horse is claimed (purchase in a public sale)
3. The Trainer switches jockeys
4. The owner switches trainers

In my first attempt at modeling this, I created the following tables:

Horses, Owners, Trainers, Jockeys and Connections.

Essentially, the Connections table was the has-many-through join table and was structured as follows:

Connections Table 1

Id Horse_id Owner_id Trainer_id Jockey_id Status_Code Status_Date Change_Code

The Horse, Owner, Trainer and Jockey foreign keys are self explanatory. The status code is 1 or 0 (1 active, 0 inactive) and the status date is the date the status changed. Change_code is and integer or string value that represent the reason for the change (private sale, claim, jockey change, etc)

The key benefit of this approach is that the Connection is represented as one record in the connections table. The downside is that I have to have a table for Owner (1), Trainer (2) and Jockey (3) when one table could due.

In my second attempt at modeling this I created the following tables:

Horses, Connections, Entities

The Entities tables has the following structure

Entities Table

id First_name Last_name Role

where Role represents if the entity is a Owner, Trainer or Jockey.

Under this approach, my Connections table has the following structure

Connections Table 2

id Horse_id Entity_id Role Status_Code Status_Date Change_Code

1 1 1 1 1 1/1/2010

2 1 4 2 1 1/1/2010

3 1 10 3 1 1/1/2010

This approach has the benefit of eliminating two tables, but on the other hand the Connection is now comprised of three different records as opposed to one in the first approach.

What believe I’m looking for is an approach that allows me to capture the Connection in one record, but also uses an Entities table with roles instead of the Owner, Trainer and Jockey tables.

I’m new to ActiveRecord and rails so any and all input would be greatly appreciated. Perhaps there are other ways that would even be better.

Thanks!