多个表的外键 [英] Foreign key for multiple tables

问题描述

我有3个表，分别为studentid，parentid和staffid，分别为student，parent和staff.我想用PK memberid, username, password创建一个member表.一个成员可以是student或parent或staff.我想用外键将这个成员表和与成员相关的表(如果成员是学生，学生表等)加入.

I have 3 tables, student, parent, and staff with PK studentid, parentid, staffid respectively. I want to create a member table, with PK memberid, username, password. One member can be a student or parent or staff. I want to join this member table and member related table (if member is student, student table and so on) with a foreign key.

当前，我在member type中使用另一列，并将其列为

Currently I'm using another column for member type and put it as

1 for student, 
2 for parent and 
3 for staff, 

并使用php编写程序.

and doing program with php.

但是我需要使用关系结构创建表的另一种有效方法.

But I need another efficient way to create tables using relational structure.

推荐答案

我假设您使用的是MySQL，但其他DBMS可能与此类似.

I assume you are using MySQL, but other DBMS may be similar.

无法使用单个列或表来完成您要问的事情.您可以使用一些选项来使用关系数据库来实现此目的:

There is no way to do what you are asking with a single column or table. You have a few options to implement this using relational databases:

选项1 :让您的成员表包括三列，这些列对职员表，家长表和学生表具有相关性约束.填充哪一列(保留其他NULL)暗示用户的类型

Option 1: Have your member table include three columns with relational constraints to the staff, parent and student tables. A user's type is implied by which column is filled (leave the others NULL)

选项2 :将要存储的数据放在职员表，家长表和学生表上的每个成员上(例如用户名和密码)

Option 2: Put the data you want to store on each member on the staff, parent and student tables (like the username and password)

每个成员都有其缺点，第一个缺点是您必须检查每一列以找出当前个人的成员类型并加入正确的表，或者在需要登录时登录三个表.您会员的memberid池不是一个. (您可以执行类似@Sherif的建议)

Each has its own downsides, the first being you will have to check each column to find out what type of member the current individual is and join the correct table, or search three tables when you need log someone in. There is also not single pool of memberid's for your members. (You could do something similar to what @Sherif suggested)

这篇关于多个表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！