3

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.

2

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.

  • 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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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