Thread: one-to-one schema design question and ORM

I'm developing a system using Ruby on Rails (with ActiveRecord) and
postgres.  (Although I think my question is still relevant for, say,
java with hibernate.)

I have two classes (tables): users and employees.   A user is an account
that can logon to the system, while an employee is...umm...an employee.

When someone is logged in, they will want to run queries like, "give me
a list of my accounts".  This means I need to link the users table with
the employees table.
 From a business rules perspective:
   Some users are not employees (like an admin user)
   Some employees are not users

 I can think of two ways to do this:

1) a 1-1 relationship where the user table contains a FK to the employee
table.  Since not all users will be employees, the FK will sometimes be
null.
In rails, the user class would "belong_to employee" while employee
"has_one user".

2) Create a link table that has FKs to both the user and employee
table.  This make sense because I'm not sure that the concept of "there
might be a linked employee" belongs in the user table.  This moves it to
a separate table designed for that purpose.  But then again, it may just
be a needless extra table.

Would you prefer one solution over the other?

Rick Schumeyer <rschumeyer@ieee.org> writes:

> I can think of two ways to do this:
>
> 1) a 1-1 relationship where the user table contains a FK to the employee
> table.  Since not all users will be employees, the FK will sometimes be null.
> In rails, the user class would "belong_to employee" while employee "has_one
> user".
>
> 2) Create a link table that has FKs to both the user and employee table.  This
> make sense because I'm not sure that the concept of "there might be a linked
> employee" belongs in the user table.  This moves it to a separate table
> designed for that purpose.  But then again, it may just be a needless extra
> table.
>
> Would you prefer one solution over the other?

It all depends on what you'll be doing and how often.  Remember that ORMs
usually "select *", so you might end up using more memory / resources than
you'd be willing to.

If the RoR mapper can do lazy loadings, then this might not be too bad...

Anyway, you might also add the extra table to make it a place to gather more
information that will be relevant to your system only.

--
Jorge Godoy      <jgodoy@gmail.com>

On Fri, Mar 09, 2007 at 10:06:52 -0500,
  Rick Schumeyer <rschumeyer@ieee.org> wrote:
>
> From a business rules perspective:
>   Some users are not employees (like an admin user)
>   Some employees are not users
>
> I can think of two ways to do this:
>
> 1) a 1-1 relationship where the user table contains a FK to the employee
> table.  Since not all users will be employees, the FK will sometimes be
> null.
> In rails, the user class would "belong_to employee" while employee
> "has_one user".
>
> 2) Create a link table that has FKs to both the user and employee
> table.  This make sense because I'm not sure that the concept of "there
> might be a linked employee" belongs in the user table.  This moves it to
> a separate table designed for that purpose.  But then again, it may just
> be a needless extra table.
>
> Would you prefer one solution over the other?

I think you need a linking table to properly represent the business rule
above. You can use unique constraints on each key in the link table,
to enforce a 1 to 1 link for the users that are employees.

hi.

Rick Schumeyer wrote:
> [...]
>  From a business rules perspective:
>   Some users are not employees (like an admin user)
>   Some employees are not users
> [...]
from my view users and employees have something in common: they are persons.

why don't create a person-table with the attributes the groups share, like a
login-name, etc.

then your users-table will have a primary-key that is also a foreign-key,
referencing the id in the person-table and the employees-table does the same.

when you want to get all user- and employee-accounts for one person you have to
build up a join on the tables. that's a simple view, don't know what your
favourite or-mapper thinks about it.

i think the above table-design can be considered 'clean' from some academic
point of view ;)

mfg

alexander