Re: [GENERAL] one-to-one schema design question and ORM
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 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? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] one-to-one schema design question and ORM
Rick Schumeyer [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] one-to-one schema design question and ORM
On Fri, Mar 09, 2007 at 10:06:52 -0500, Rick Schumeyer [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq