Database design question

Hi, I'm new to Ruby, having been doing a lot of reading over the last
several weeks and just starting to try out a few things. I have a
project that I would like to do, but I'm not sure what's the best way to
accomplish the relationships that would like to do. The project is kind
of a contact management application that goes further down the path in
certain areas. Vague, I know, but there real question is:

A contact can belong to one or more categories and, with some of those
categories (but not all), additional information is required and/or
desired to be collected, including foreign keys to other tables. I have
been reading through Agile Web Development and my situation is somewhat
like the single table inheritance (STI) example provided, though
extracted one layer. That is, in the STI example, a person could exist
in only one of the categories.

A more concrete example would be, say, that for a school, a contact can
be an inquiry, an applicant, a student, a teacher, a mentor, etc. In
this scenario, a person could be a student and a mentor at the same
time. Data is collected for applicants and when they are approved, they
become a student (their application data is retained). A student has
other specific information to be collected and can be linked to other
data in the system.

TIA.

Henry Happ wrote:

A more concrete example would be, say, that for a school, a contact can
be an inquiry, an applicant, a student, a teacher, a mentor, etc. In
this scenario, a person could be a student and a mentor at the same
time. Data is collected for applicants and when they are approved, they
become a student (their application data is retained). A student has
other specific information to be collected and can be linked to other
data in the system.

TIA.

My $0.02:

I'd say that you have one main "generic" model (Person), and several
other models that are related to a person, but not quite instances of a
person such as STI would imply, as a person could be multiple things at
once.

Each of these other related models has their own set of unique
information (prospect information, application data, standardized test
scores, current schedule, academic records, teaching schedule, etc) that
shouldn't be mashed together, to say nothing of the temporal nature of
being a student, or a teacher, or a mentor (you could be some of these
things at the same time, or repeated over distinct instances of time).

But everyone has a name, one or more addresses, emails, phones, etc that
would be an attribute of a person (from the application point of view).
Common attributes go in the generic person, domain specific (student,
teacher, applicant, prospect) attributes go in a related table linked to
the Person.

Ar Chron wrote:

My $0.02:

I'd say that you have one main "generic" model (Person), and several
other models that are related to a person, but not quite instances of a
person such as STI would imply, as a person could be multiple things at
once.

Each of these other related models has their own set of unique
information (prospect information, application data, standardized test
scores, current schedule, academic records, teaching schedule, etc) that
shouldn't be mashed together, to say nothing of the temporal nature of
being a student, or a teacher, or a mentor (you could be some of these
things at the same time, or repeated over distinct instances of time).

But everyone has a name, one or more addresses, emails, phones, etc that
would be an attribute of a person (from the application point of view).
Common attributes go in the generic person, domain specific (student,
teacher, applicant, prospect) attributes go in a related table linked to
the Person.

You have absolutely captured what I was trying to say and have restated
the needs much more clearly than I did.

If it's not too bold to ask, could you please explain in a more concrete
way how to implement the structure describe in Rails?

Henry Happ wrote:

If it's not too bold to ask, could you please explain in a more concrete
way how to implement the structure describe in Rails?

Well... I'd start with a modeling exercise to figure out what are the
attributes of a:
A Prospective Student <- Maybe prospects are kept segregated from People
for the sheer reason that you may have a bazillion prospective students
of which you have comparatively few who are ever of concern to your
application later (as students).

An Applicant <- I think I'd use an Applications model (related to a
Prospective Student), not the person as an applicant. An applicant is
just a Prospective Student who sent in a bit of paper and a check...
maybe more than once. If they become a Student, then relate the
application data to the student record created from the prospective
student.

A Student, A Teacher, A Whatever - These models would have substantive
amounts of data not shared by each other, or Person.

A Mentor <- being a mentor may not be a type of person, but rather a
name for a participant in an instance of a mentoring relationship
between two people (I'm not sure being a mentor adds much information
about the Person in addition to the existence of the relationship).

Then distill those common attributes into a Person model. What's left
over from each type of person you create would go into a new model for
that perspective of a person. Each of those models would contain a
person_id field to get back to the person (and use a "belongs_to
:person" in the model.rb). Person.rb would have a has_one :something
for each of these related models... something like:

class ProspectiveStudent < ActiveRecord::Base
  has_many :applications
end

class Application < ActiveRecord::Base
  belongs_to :prospective_student
  belongs_to :student
end

class Person < ActiveRecord::Base
  has_one :student
  has_one :teacher
end

class Student < ActiveRecord::Base
  belongs_to :person
  has_one :application # in terms of applications, one got the person
to become a student... when prospective student becomes student,
current application gets flagged for student
  # table contains person_id : integer, and other info for students
end

class Teacher < ActiveRecord::Base
  belongs_to :person
  # table contains person_id : integer, and other info for teachers
end

Or something like that...