双主键设计

我的数据库有一个跟踪部门和用户ID的表.这里的问题是虽然department和user_id列可以有重复项,但它们的组合却不能.这就是说,

DepartmentA    0001
DepartmentA    0002
DepartmentB    0001
DepartmentB    0002

是我表中的有效数据.但

DepartmentA   0001
DepartmentA   0001  

是不是有效.

department和user_id的组合形成记录的唯一标识符.在另一个表格中,我需要使用这个唯一标识符来跟踪用户的活动,例如他们进入建筑物的时间,他们离开建筑物的时间等.

我在想的是我创建了下表

CREATE TABLE user (
 user_id INT( 4 ),
 department VARCHAR( 25 ) NOT NULL ,
 combined_id int(4) ,
 UNIQUE ( combined_id ) ,
 Primary key(user_id, department)
 );



CREATE TABLE user_activity(
combined_id int(4),
activity varchar(25),
Foreign Key (combined_id) references user(combined_id)
);

所以我正在考虑为我的目的使用双主键.恕我直言,这是保证数据完整性的最佳方式.但据我所知,使用双主键可能很难与ORM一起使用,即它们不是完全支持的,并且必须为它编写自定义查询.

鉴于我的情况,上述设计是最好的吗？

我会使用代理主键,并对应该保持唯一的那两个字段的组合设置唯一约束.

也就是说,我不会将我的员工编号视为主键.这有利于很多场景.

所以,这就是我要做的

CREATE TABLE Employee
(
  EmployeeId INT ,
  EmployeeNumber VARCHAR(4),
  DepartmentId INT,
  EmployeeName
)

CREATE TABLE Department
(
   DepartmentId INT,
   DepartmentName
)

在Employee表中,EmployeeId是主键.此列只包含一个数字,在问题域中没有任何意义.它在数据库中只具有管理含义(唯一标识一个Employee).

EmployeeNumber包含用于标识员工的业务/域号.请注意,我已将此字段设为VARCHAR字段,因为您在示例中使用零来对该数字进行LPAD.所以实际上,它不应该是一个数字字段.:)

除此之外,Employees表中还有一个DepartmentId列,它是Department表的外键.在Employee表中,您应该在EmployeeNumber/DepartmentId字段上放置一个Unique约束.(因此,对两个列的组合有一个约束).

然后,User_Activity表可以如下所示:

CREATE TABLE User_Activity
(
  EmployeeId INT,
  ActivityId INT
)

(我知道CREATE TABLE语句实际上并不是真正正确的SQL语句,但这只是为了说明目的).