# Database design question

**URL:** <https://www.ruby-forum.com/t/database-design-question/149777>\
**Category:** Rails\
**Created:** [September 29, 2008, 7:32pm UTC](https://www.ruby-forum.com/t/database-design-question/149777 "2008-09-29T19:32:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![hankster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b2d939/32.png) [@hankster](https://www.ruby-forum.com/u/hankster)\
**Post date:** [September 29, 2008, 7:32pm UTC](https://www.ruby-forum.com/t/database-design-question/149777/1 "2008-09-29T19:32:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hankster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b2d939/32.png) [@hankster](https://www.ruby-forum.com/u/hankster)\
**Post date:** [September 29, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/database-design-question/149777/2 "2008-09-29T22:36:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hankster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b2d939/32.png) [@hankster](https://www.ruby-forum.com/u/hankster)\
**Post date:** [September 30, 2008, 1:47am UTC](https://www.ruby-forum.com/t/database-design-question/149777/3 "2008-09-30T01:47:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![hankster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b2d939/32.png) [@hankster](https://www.ruby-forum.com/u/hankster)\
**Post date:** [September 30, 2008, 6:53pm UTC](https://www.ruby-forum.com/t/database-design-question/149777/4 "2008-09-30T18:53:28Z")

</div>

Hi Andrew,

Thanks for your input. You have been extremely helpful and have given  
me good insight into the direction in which to go. I’ll start working  
on simple prototyping to see how this will all fit together.

- Henry

---

<div class="post-metadata">

**Author:** ![hankster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b2d939/32.png) [@hankster](https://www.ruby-forum.com/u/hankster)\
**Post date:** [September 30, 2008, 5:38am UTC](https://www.ruby-forum.com/t/database-design-question/149777/5 "2008-09-30T05:38:20Z")

</div>

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…