0

Some of the Users in my database will also be Practitioners.

This could be represented by either:

  • an is_practitioner flag in the User table
  • a separate Practitioner table with a user_id column

It isn't clear to me which approach is better.

Advantages of flag:

  • fewer tables
  • only one id per user (hence no possibility of confusion, and also no confusion in which id to use in other tables)
  • flexibility (I don't have to decide whether fields are Practitioner-only or not)
  • possible speed advantage for finding User-level information for a practitioner (e.g. e-mail address)

Advantages of new table:

  • no nulls in the User table
  • clearer as to what information pertains to practitioners only
  • speed advantage for finding practitioners

In my case specifically, at the moment, practitioner-related information is generally one-to-many (such as the locations they can work in, or the shifts they can work, etc). I would not be at all surprised if it turns I need to store simple attributes for practitioners (i.e., one-to-one).

Questions

  1. Are there any other considerations?
  2. Is either approach superior?
Vorpulus Lyphane
  • 660
  • 6
  • 19

3 Answers3

1

You might want to consider the fact that, someone who is a practitioner today, is something else tomorrow. (And, by that I don't mean, not being a practitioner). Say, a consultant, an author or whatever are the variants in your subject domain, and you might want to keep track of his latest status in the Users table. So it might make sense to have a ProfType field, (Type of Professional practice) or equivalent. This way, you have all the advantages of having a flag, you could keep it as a string field and leave it as a blank string, or fill it with other Prof.Type codes as your requirements grow.

You mention, having a new table, has the advantage for finding practitioners. No, you are better off with a WHERE clause on the users table for that.

Your last paragraph(one-to-many), however, may tilt the whole choice in favour of a separate table. You might also want to consider, likely number of records, likely growth, criticality of complicated queries etc.

Whirl Mind
  • 884
  • 1
  • 9
  • 18
  • Thanks for your answer. Regarding your first paragraph, isn't that easily covered in either case by an is_consultant flag or Consultant table respectively? Why do you say a WHERE clause is better? Why does the existence of numerous one-to-many relationships tilt things in favour of a separate table? – Vorpulus Lyphane Apr 06 '17 at 13:15
  • For a practitioner, a flag, Again, a flag for consultant, You may go on adding flags for each Prof Type. In such cases, a single text field would have served the same job. A table of practitioners, one more for consultants : Say you want to find users who are practitioners OR consultants. It will be two left outer joins from users with practitioners and consultants. Could have been done with, where ProfType In ('PRAC', 'CONS'). Nature of one-to-many: If target field has its own attributes, use a table, else use a field. Use simple design for simple setup, comprehensive for complex. – Whirl Mind Apr 06 '17 at 23:50
1

I tried to draw two scenarios, with some notes inside the image. It's really only a draft just to help you to "see" the various entities. May be you already done something like it: in this case do not consider my answer please. As Whirl stated in his last paragraph, you should consider other things too.

Draft logical schema

etsa
  • 5,020
  • 1
  • 7
  • 18
1

Personally I would go for a separate table - as long as you can already identify some extra data that make sense only for a Practitioner (e.g.: full professional title, University, Hospital or any other Entity the Practitioner is associated with).

So in case in the future you discover more data that make sense only for the Practitioner and/or identify another distinct "subtype" of User (e.g. Intern) you can just add fields to the Practitioner subtable, or a new Table for the Intern.

It might be advantageous to use a User Type field as suggested by @Whirl Mind above.

I think that this is just one example of having to identify different type of Objects in your DB, and for that I refer to one of my previous answers here: Designing SQL database to represent OO class hierarchy

p.marino
  • 6,244
  • 3
  • 25
  • 36