我们可以将主键添加到损坏的表中吗

Question

我有一张表格：

Emp_Tutor: Tutor table.
Emp_Tu_De :Tutor details table.
Emp_Tu_St: Tutor status table.
Emp_School: School table.

正如我们所知，一所学校有许多导师，每个导师可能在一所学校或另一所学校工作，也可能在两所或三所学校工作。

所以家教桌我把它当作是学校和家教details>之间的一张破桌子

导师状态表我们创建它来插入导师教学状态，如(课程，班级，教学时数等)。

所以我的问题是：

我可以在Tutor表中添加一个主键来建立(Tutor表和Tutor status表)之间的关系吗？

别忘了辅导桌是一种破裂的关系。

look at image attachment.

Answer 1

我发现尝试存储像status这样的东西通常是一个错误。例如，“当前”、“以前”、“重新雇用”的雇佣“状态”通常更好地实现为具有开始日期和结束日期的雇佣表格。

破碎的表和破碎的关系在数据库设计中不是常见的英语术语。我不明白你说的是什么意思。

下面是PostgreSQL代码。SQL Server将使用datetime数据类型代替标准SQL的timestamp数据类型。可能还有其他一些小的区别。

-- Nothing surprising here.
create table schools (
  school_id integer primary key,
  school_name varchar(20) not null unique
  -- other columns go here
);

-- Nothing surprising here.
create table tutors (
  tutor_id integer primary key,
  tutor_name varchar(20) not null
  -- other columns go here
);

-- Nothing surprising here.
create table tutor_details (
  tutor_id integer primary key references tutors (tutor_id),
  tutor_phone varchar(15) 
  -- other columns go here
);

-- Predicate: School <school_id> employed <tutor_id> 
-- starting on <start_date> and ending on <end_date>.
-- Allows multiple periods of employment.
create table school_tutors (
  school_id integer not null references schools (school_id),
  tutor_id integer not null references tutors (tutor_id),
  start_date date not null default current_date,
  end_date date not null default '9999-12-31',
  -- You can make a good, practical argument for including end_date
  -- in the primary key, but that's a different issue.
  primary key (school_id, tutor_id, start_date)
);


-- Only makes sense in the context of employment, so a composite
-- foreign key references school_tutors. In production, I'd try
-- to use more check constraints on the timestamps.
create table tutor_office_hours (
  school_id integer not null,
  tutor_id integer not null,
  start_date date not null,
  foreign key (school_id, tutor_id, start_date) 
    references school_tutors (school_id, tutor_id, start_date),
  office_hours_start_time timestamp not null,
  office_hours_end_time timestamp not null
    check (office_hours_end_time > office_hours_start_time),
  primary key (school_id, tutor_id, office_hours_start_time)
);