Geek Answers Handbook

How do I create a MySQL database?

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

In any case, my question is this: I plan to have a database that deals with (at the moment) two types of users, for example, Administrators and Users . My goal is to have ONE table containing all users exactly named "users". The following is an example of my MySQL command (which I have not tested yet, so there are probably errors):

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 column "user_type" above will refer to another table called "user_types", which lists the different types of users for the website (I do this in order to add additional types of users 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 goal is to associate Users with Admins; one "user" (child) may have one "administrator" (parent), but one "administrator" (parent) may have several associated with "users" (children). The goal for me is to create a simple meeting calendar, and for this I need to connect users to my administrators (a one-to-one relationship in the sense that the appointment is between one user and one administrator). Now the question is:

1) Is it possible to achieve this by having ONE table for all users? If so, how can I do it well? 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 code above looks weird to me; can I make such a foreign key associated with one table without any dangers? The following is also the SQL code for the meeting 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, each entry in the “meeting” table indicates a specific task between the “Administrator” and the “User”.

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

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

+4
source share
2 answers

Your proposed assignment table will work if you had a lot of relationships between users and admins. Since you described the relationship as a one-to-many relationship (a single administrator can have many users), I simply add the admin_id column to your users table and make it an internal key with a binding 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;
+1
source

In the users table add admin_userid that user links (user_id)

Thus, each user points to the user table to the user to which they belong.

Using this column, the doctor can list all of his patients, and the appointment table can be used with the appointment.

But will any user ALWAYS meet with the same doctor / administrator?

How about a vacation?

+1
source

More articles:


All Articles