views:

364

answers:

2

I have multiple user account types, each with different information. For example, business contacts link to businesses, school administrators and students link to schools. Students have physical addresses, but business contacts and school admins use the organizations address. There's other information unique to each type as well.

I'm leaning toward separate tables for students, school admins and business contacts, but using Authlogic, I have a Users table with authentication information (all need to log in).

The question is really how best to link this single authentication table with the individual profiles. It seems like a one-to-one relationship requires a single table (e.g. Users <-> Students or Users <-> Business_contacts). I want to have something of a "one-to-one-of-the-following" relationship (Users <-> Students or Business_contacts). Is there a good way to do this using a join table or other construct?

Alternatively, I could unify the common information in the Users table and come up with a "profile" column in XML to support the unique information. My thought was that keeping everything in clean DB columns would simplify selects/inserts.

Thoughts, ideas?

+1  A: 

business contacts link to businesses, school administrators and students link to schools.

This means you have different roles of users rather than different users.

Students have physical addresses, but business contacts and school admins use the organizations address There's other information unique to each type as well.

Which means different roles have different data and behavior, leading to different OO classes.

The question is really how best to link this single authentication table with the individual profiles. I want to have something of a "one-to-one-of-the-following" relationship (Users <-> Students or Business_contacts). Is there a good way to do this using a join table or other construct?

The simplest model I can think of if the following:

  • User (Username, Email, Password, Names and other common data for every user)
  • Participant, abstract. (has relation to one user - defines the role of the participant, behavior and its common data). Should provide the interface for participants.
  • Student (inherits from Participant, adds its own behavior and data)
  • SchoolAdministrator (inherits from participant)
  • And so on

In the Object Oriented World (non RDBMS) this has a simple advantage: polymorphism. Having a user you don't need to know exactly who he is. You just do things like this:

user.participant.can_manage_stuff?
user.participant.order_book(harry_potter)

And the appropriate actions will take place which will be implemented in Student, SchoolAdministrator or other class inherited from Participant.
Another things is that it is very easy to add new roles for the system. Just inherit from Participant and implement its interface.

Now, when Object Oriented Design is done, let's have a look at the data storage. I assume you use RDBMS.

So now you have 2 types of links:

  1. One-to-one association between User and Participant.
  2. Generalization (inheritance) between Participant and Student, SchoolAdministrator.

Implementation of 1st is as simple as having has_one and/or belongs_to association for the following table (via participant_id column):

users: id | username | email | password | etc | participant_id (non-null) FK_TO_participants table |

So you can easily implement that.

Now, the 2nd link can be implemented in RDBMS in number of different wasy.

But fortunately or unfortunately ActiveRecord supports only one option to do that. And it is hierarchy per table mapping strategy that uses discriminator column to distinguish the type (stored in type column by convention).

So you will have the 2nd table that looks like this:

perticipants: id | type | student_card_number (null) | administrator_number (null) | etc

This table will have all the columns for all possible participants. This is the simplest and easiest implementation of hierarchy in DB. But not the most optimal from some points of view. As I said with ActiveRecords it is only one option available anyway.

So as a result of this design you will end up with 2 database tables (users, participants) and at least 3 classes (User, Participant, Student).

And, of course, you can vary from here a lot but that should deliver my point.

And yes, please no XML in database, don't waste your nerves and valuable personal time.

Cheers.

Dmytrii Nagirniak
That's just what I was looking for. I think my confusion was caused by my not appreciating the strong ORM of Rails. I was thinking of it in an OOP way, but couldn't translate that to RDBMS. It looks like Rails supports polymorphic associations just fine, so I can, actually, just "inherit." Thanks for the detailed description, it helps a lot to clear up my thinking.
JohnMetta
+1  A: 

If you really need seperate models for each role you use, then I would recomend to use polymorphic association (read more here). So:

# User model
belongs_to :owner, :polymorphic => true

# Student model
has_one :user, :as => :owner

# Any other model
has_one :user, :as => :owner

And you need to add to users table:

 t.integer :owner_id
 t.string  :owner_type
klew
Exactly what I was looking for. I just hadn't looked far enough into Active Record. Thanks!
JohnMetta
By the way, it looks like the docs state the users table can be simplified to use "t.references :owner, :polymorphic => true" Just read that and find it easier to read. Cheers.
JohnMetta
`references` just creates integer field and adds `_id` to field name. So it's the same. And it really is easier to read :)
klew