From: | Rick Schumeyer <rschumeyer(at)ieee(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | one-to-one schema design question and ORM |
Date: | 2007-03-09 15:06:52 |
Message-ID: | 45F1780C.7000308@ieee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-03-09 15:15:41 | Re: Weird behaviour on a join with multiple keys |
Previous Message | Walter Vaughan | 2007-03-09 13:57:10 | Re: Anyone know a good opensource CRM that actually installs with Posgtres? |