How should I structure my MySQL database?

Question

Hi all!

First of all, sorry if this might be a stupid question. I'm very new to the world of MySQL, so...

Anyway, my question is this: I'm planning on having a database that deals with (for now) two types of users, let's say Admins and Users. My aim is to have ONE table containing all users, aptly named "users". Below is a rough outline of my MySQL command (which I haven't tested yet so errors are likely):

CREATE TABLE users {
 user_id  int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 user_type  int NOT NULL REFERENCES user_types(user_type_id),
 ssn      char(10) NOT NULL,
 password  varchar(40) NOT NULL,
 first_name  varchar(30) NOT NULL,
 last_name varchar(30) NOT NULL,
 address     varchar(80) NOT NULL
} engine = InnoDB;

The "user_type" column above will refer to another table called "user_types", which lists the different user types for the website (I'm doing this for the sake of having the option to add more user types later):

CREATE TABLE user_types {
   user_type_id     int UNSIGNED NOT NULL PRIMARY KEY,
   user_type_desc varchar(10) NOT NULL
} engine = InnoDB;

INSERT INTO user_types (user_type_id, user_type_desc) VALUES(1,'Admin'),(2,'User');

My aim is to link "Users" with "Admins"; one "User" (child) can have one "Admin" (parent), but one "Admin" (parent) can have several "Users" associated (children). The goal for me is to create a simple appointment calendar, and for that I need to connect users with their admins (one-to-one relationships in the sense that the appointment is between one user and one admin). Now the question is:

1) Is it possible to achieve this by having ONE table for all users? If so, how do I do it in a good way? Right now I was thinking of creating a table called "assignments":

CREATE TABLE assignments {
assign_id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
patient_id int NOT NULL REFERENCES users(user_id),
doctor_id int NOT NULL REFERENCES users(user_id)
} engine = InnoDB;

But the above code looks strange to me; can I do that kind of foreign key linking to the same table without any dangers? Below is also the SQL 'code' for the "appointments" table:

CREATE TABLE appointments {
appointment_id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
assign_id     int FOREIGN KEY REFERENCES assignments(assign_id),
date_time     datetime NOT NULL,
description     varchar(200) NOT NULL
};

That is, every entry in the "appointments" table points to a certain assignment between an "Admin" and a "User".

2) How can I achieve the one-to-many relationship between "Admins" and "Users" in an easy way, or rather, a proper way?

Any help or suggestions would be greatly appreciated, and sorry if these questions are stupid!

Answer 1

Your proposed assignments table would work if you had a many-to-many relationship between Users and Admins. Since you've described the relationship as 1-to-many (one Admin may have many Users), I would simply add an admin_id column to your users table and make it a self-referencing foreign key back to the users table.

CREATE TABLE users {
 user_id  int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 user_type  int NOT NULL REFERENCES user_types(user_type_id),
 ssn      char(10) NOT NULL,
 password  varchar(40) NOT NULL,
 first_name  varchar(30) NOT NULL,
 last_name varchar(30) NOT NULL,
 address     varchar(80) NOT NULL,
 admin_id int REFERENCES users(user_id)
} engine = InnoDB;

Answer 2

In the users table add admin_userid that References users(user_id)

That way, each user points back to users table to the admin user they belong to.

Using this column a doctor can list all his patients and the assignements table can be used with appointments.

But will a certain user ALWAYS get a meeting with the same doctor/admin?

What about vacations?