Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have read this (Database design: 3 types of users, separate or one table?)

I decided to put admin and normal user in one table since the attributes are similar:

fullname,
address,
phone,
email,
gender
...

Then I want to draw ERD, suddenly my mind pop out a question. How to draw? Customer make appointment and admin approve appointment. now only two tables, and admin, customer in same table. Help.

share|improve this question
add comment

3 Answers

That's what I'd do:

table user (
  id primary key,
  name,
  email,
  -- etc
);

table approver (
  id primary key foreign key user(id),
  appointed_date, 
  -- etc
);

table appointment (
  -- whatever PK
  created_by foreign key user(id), 
  approved_by foreign key approver(id),
  -- etc
);

Now any user can create an appointment, but only users also present in approver table can approve it.

share|improve this answer
    
This is a good conceptual model and it works well to enforce integrity at the data level (+1), but practically, normalizing this relationship makes it more difficult to retrieve a user as a single object in code. You now have to query a join between the two tables to return a single conceptual record, or change the "Approver is a User" conceptual relation to "Approver has a User". It's easier to simply add an "IsApprover" bit field to user, and if you must enforce that the approved_by user reference is an approver in the data layer, use a check constraint (or CRUD stored procs). –  KeithS Jul 9 '12 at 17:23
    
@KeithS: yes, there's a trade-off between ease of OOP representation and data integrity; other answers represent your point of view in more detail. I don't know if a plain user and an approver are conceptually the same. With more creative OR mapping you can make the Approver class a subclass of User. It's not even necessary to think in OOP terms, though I understand that OOP is the prevalent concept today, implied by many languages and frameworks. –  9000 Jul 9 '12 at 18:56
add comment

I want to draw ERD

You need to clearly specify whether a normal user can be an admin one day or not. However, we don't know from your description. This is a key point.
If a regular user will never be an admin you better use 2 different tables. If you want to use 1 table here are 2 solutions. The first uses the subtype concept which requires at least 1 different column between the two tables or that a subtype is a child table.

The data you provided does not show any different attributes between the two types of users, so you can't use the first solution based on the data given.

The second solution is more generic. If you plan to have the RegularUser and admin changing types/roles, then you need to create an m-m relationship between RegularUser and UserType. (Note AppointmentID is PK for Appointment table).

enter image description here

share|improve this answer
add comment

what about

fullname,                 user_type_id(PK),
address,                  user_type
phone,
email,
gender,
user_type (FK)
...

where user_type will be admin or customer or any

share|improve this answer
    
Good luck formulating a database constraint preventing non-admin users from approving an appointment. –  9000 Jul 9 '12 at 5:29
    
@9000 Isn't that something you'd enforce in the application instead of in the database? –  Kristof Claes Jul 9 '12 at 6:38
    
@KristofClaes: I'm a bit on the paranoid side when it comes to important data. A database should not allow any invalid data at all if possible. Applications can and will misbehave, people hastily fixing things right in the database will do mistakes, etc. –  9000 Jul 9 '12 at 15:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.