我应该如何构建我的MySQL数据库？

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

首先,对不起,如果这可能是一个愚蠢的问题。我对MySQL的世界很新,所以......

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):

无论如何,我的问题是:我打算建立一个数据库来处理(现在)两种类型的用户,比如管理员和用户。我的目标是让一个包含所有用户的表,恰当地命名为“users”。下面是我的MySQL命令的大致轮廓(我还没有测试过,所以可能出现错误):

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):

上面的“user_type”列将引用另一个名为“user_types”的表,它列出了网站的不同用户类型(我这样做是为了以后可以选择添加更多用户类型):

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":

1)是否可以通过为所有用户提供一个表来实现这一目标?如果是这样,我该如何以良好的方式做到这一点?现在我正在考虑创建一个名为“赋值”的表:

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:

但上面的代码对我来说很奇怪;我可以做那种没有任何危险连接到同一张桌子的外键吗?下面是“约会”表的SQL'代码':

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?

2)如何以简单的方式实现“管理员”和“用户”之间的一对多关系,或者更确切地说,是一种正确的方式?

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

任何帮助或建议将不胜感激,如果这些问题是愚蠢的抱歉!

2 个解决方案

#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.

如果您在用户和管理员之间存在多对多关系,那么您建议的分配表将起作用。由于您已将关系描述为1对多(一个Admin可能有很多用户),因此我只需将admin_id列添加到users表中,并将其作为自引用外键返回到users表。

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;

#2

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

在users表中添加引用用户的admin_userid(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?

假期怎么样?