0

I have three tables in my database: Contacts (master table), Users and Staff. The Contacts table has a primary key that is referenced as a foreign key from the Users and Staff tables.

Contacts 

ContactID (Primary Key)
First Name
Last Name 
Email

Users:

ContactID (Foreign Key) 
Username 
Password

Staff:

ContactID (Foreign Key) 
Position 
Comments

Pulling data from these tables is fairly simple with INNER JOIN and depends what data you need from the Users or Staff table. Updating and inserting new records is something that I'm not sure which way to approach.

Since the Contacts table is my master table and holds unique ID's but at the same time is shared between Users and Staff, I'm not sure about this situation.

Let's say I want to enter new user record. I need to enter First, Last name and email into the Contacts table, and User Name and Password into the Users table. At the same time I have to check if First, Last name and email already exist in the Contacts table, since maybe this Contact record has been previously entered for a Staff record.

I'm not sure how to prevent duplicates but at the same time there might be user or staff with the same name. Should I limit/filter by email and not let them enter the records if the email already exist in contacts table? Or is there a better way to handle this?

The whole point of having three tables is to prevent redundant data since User might be a Staff, but Staff might not be a User if that make sense. If anyone can help me pick the best approach please let me know. Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • From reading your post, I'm not entirely sure that 3 tables are needed. In fact, it might actually be that only 1 table is. Is the relationship between Contact, User and Staff 1 to 1? If a Contact can be a member of staff, but not a user, then why not allow the user specific columns to be NULLable, and vice versa? (Assuming that a Staff member can be a User as well). – Thom A Apr 06 '18 at 15:53
  • Related Question: https://stackoverflow.com/questions/49694213/users-and-staff-in-the-same-table-or-separate-tables – Dave C Apr 06 '18 at 15:54
  • @Larnu If I understand your comment you suggest storing all data in one table. Then have a field that will mark if user is Staff or not. Correct? – espresso_coffee Apr 06 '18 at 15:55
  • That's one option, yes, if my understanding is correct. – Thom A Apr 06 '18 at 15:55
  • @DaveCullum It is related but different topic since I asked about design and here is more how to handle Insert/Update statement. – espresso_coffee Apr 06 '18 at 15:56
  • @Larnu Yes, the whole reason why this is more complex is because User can be a Staff member and if we have that record I would like to create the duplicate/redundant data in database. On the other side Staff record doesn't have to be a user. That means I do not need password and all other user information for that record. Does that make sense? – espresso_coffee Apr 06 '18 at 15:57
  • @espresso_coffee Yep, I got that, but it adds details about the design. Just reading this question alone its unclear why you went with 3 tables, when typically 1 would be suggested. – Dave C Apr 06 '18 at 15:58
  • @DaveCullum The only reason why I was thinking about 3 tables is because I thought it might be security risk to store Users and Staff in the same table... – espresso_coffee Apr 06 '18 at 15:59
  • It shouldn't be. What does it look like if a staff member becomes a user or vice-versa? Are you going to setup methods/procedures to change them around and delete old references? A boolean for isUser and isStaff would handle that nicely... you can set them on/off to control app functionality. – Dave C Apr 06 '18 at 16:04
  • @DaveCullum Or maybe have one field `Type` that will have reserved numeric value if record is user or staff? – espresso_coffee Apr 06 '18 at 16:05
  • What if they're both? Is that conceivably possible? Will you have an admin with both "options" enabled, or superusers? What about future addons? You might consider a bitwise field if you're opposed to booleans... lots of options. – Dave C Apr 06 '18 at 16:08
  • @DaveCullum Still trying to think through all possible solutions and scenarios. Not sure if I'm missing something... – espresso_coffee Apr 06 '18 at 19:05

1 Answers1

1

You have to start by using a SELECT query and whatever business rules you want to test to decide if you will perform the INSERT at all, or UPDATE an existing record instead.

Then if you are going to INSERT, you have to INSERT into Contacts first, get the newly inserted ID, and then INSERT into the other two tables.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52