Question

我正在研究一项有两种用户、教师和学生的申请。

教师和学生都有第一、最后的名称、独一无二的电子邮件和独一无二的用户名(通过独一无二的字眼也意味着教师与学生有相同的用户名/电子邮件)。

Besides these common fields students will have 4 more fields: A, B, C and D.

教师将有E、F和G。

为此设计一个数据库的最佳方式是什么? 如果我使用2个表格(学生1个,教师1个,所有领域各1个)或3个表格(用户表格),用于用户名称和电子邮件等共同领域,然后与学生和教师表格有一对一的关系,这些表格只包含特定领域。

This database will be used in a system where students/teachers will have to login. I guess it s better to look for credentials in just one table, than to have 2 different login points in the application, each querying a different table.

Actually I ll be using hibernate to generate the tables from java classes, so from an object-oriented point of view using 3 tables sounds better.

I m looking forward to seeing other opinions on this.

感谢

Answer 1

自愿者(至少3.x)建议每组级别战略的表格。每一行各有一栏,分层各行各有一栏,因此,你将浪费一部分空间。但这只是简单地弥补了这一下滑。

The other thing you can do is just make each class have its own table. i.e. hibernate doesn t know about the inheritance. That might complicate other parts of your application though...

Answer 2

CREATE TABLE People
(
    PersonID      INTEGER NOT NULL PRIMARY KEY, -- Add local incantation for auto-allocated numbers
    FirstName     VARCHAR(32) NOT NULL,
    LastName      VARCHAR(32) NOT NULL,
    Email         VARCHAR(64) NOT NULL UNIQUE,
    UserName      VARCHAR(16) NOT NULL UNIQUE,
    PersonType    CHAR(1) NOT NULL CHECK(PersonType IN( S ,  T )) -- S student, T teacher
);

CREATE TABLE Student
(
    PersonID      INTEGER NOT NULL REFERENCES People,
    A             ...,
    B             ...,
    C             ...,
    D             ...
);

CREATE TABLE Teacher
(
    PersonID      INTEGER NOT NULL REFERENCES People,
    E             ...,
    F             ...,
    G             ...
);

一种制约因素是,教师也被迫这样做。个人信息列栏在<代码>的人群中指一行 PersonType = T, 学生也是如此。个人信息数据库在人名栏中注明:<代码> PersonType = S 。

There isn t a particularly clean way to enforce that automatically. Your options include adding a column PersonType to each of Teacher and Student that will always hold T or S and then creating a foreign key that references People(PersonID, PersonType). This is ugly because the value in the Teacher or Student table is constant, and because the PersonID alone is unique.

否则,你就会执行该守则中的制约因素。我可能使用该守则,并定期检查违反限制的条目(在教师中发现学生在人民桌上,或在学生中发现在人民桌上教的人)。

朝圣的这种地图是一个单独的问题。你可能认为,有两种观点是有益的:

CREATE VIEW StudentInfo AS
    SELECT P.*, S.A, S.B, SS.C, S.D
      FROM People AS P
      JOIN Student AS S
        ON P.PersonID = S.PersonID AND P.PersonType =  S ;

CREATE VIEW TeacherInfo AS
    SELECT P.*, T.E T.F, T.G
      FROM People AS P
      JOIN Teacher AS T
        ON P.PersonID = T.PersonID AND P.PersonType =  T ;

当然,你可以坚决地认为,对人身限制是不必要的。

Answer 3

只是意见。我会把所有内容放在一个桌上,但会利用解放性遗产和按等级划分的表格绘制多种仇恨地图。

rel=“nofollow”http://docs.jboss.org/hibernate/core/3.3/vis/en/html/inheritance.html。

友情链接