SQL数据库设计问题

本文介绍了SQL数据库设计问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用Microsoft SQL Server 2000并对我们的数据库有疑问 设计。 这是我们目前拥有的表和列的子列表： - 员工 ---------- Ee_Code PRIMARY KEY Ee_Name NOT NULL 分行 ------------ Branch_ID PRIMARY KEY Branch_Name非空 创建一个新的EmployeeBranch表是否更好，该表包含 员工及其可以工作的相关分支的列表（因为他们应该能够在不止一个分支机构工作），或者最好在Employee表中创建几个与他们可以工作的分支相对应的 at。 例如， EmployeeBranch ------------ --- Ee_Code Branch_ID 或 员工 ---------- Ee_Code Ee_Name Ee_Branch1 Ee_Branch2 Ee_Branch3等... 对我而言，使用我的第一个建议显然更好。但是，我如何确保每个员工在 EmployeeBranch表中至少有一个条目，并且每个员工只能出现一次 每个单独的分支（即，没有重复的EmployeeBranch数据）？ 是否可以在我们的表上设置约束和关系以允许 这个怎么样，我该怎么做呢？ 提前感谢任何建议 丹

I am using Microsoft SQL Server 2000 and have a question about our database design. Here is a sublist of tables and columns we currently have:- Employee ---------- Ee_Code PRIMARY KEY Ee_Name NOT NULL Branch ------------ Branch_ID PRIMARY KEY Branch_Name NOT NULL Is it better to create a new EmployeeBranch table that contains a list of employees and the relevant branches they can work at (as they should be able to work at more than one branch), or is it better to create several columns in the Employee table that correspond to the branches they can work at. For example, EmployeeBranch --------------- Ee_Code Branch_ID or Employee ---------- Ee_Code Ee_Name Ee_Branch1 Ee_Branch2 Ee_Branch3, etc... To me it obviously appears better to use my first suggestion. But, how do i go about ensuring that each employee has at least one entry in the EmployeeBranch table, and that each employee can only have one occurrence of each individual branch (ie. there''s no duplication of EmployeeBranch data)? Is it possible to setup constraints and relationships on our tables to allow for this and how do i go about doing it? Thanks in advance for any suggestions Dan

推荐答案

您可以像在任何其他 表中一样阻止EmployeeBranches表中的重复：使用PK或UNIQUE约束。但是，你的约束条件是 ，允许在没有相应条目的情况下添加员工 EmployeeBranches，否则你永远不能在 $ b中添加新行$ b员工表。 CREATE TABLE员工（ee_code CHAR（10）PRIMARY KEY，ee_name VARCHAR（50）NOT NULL） CREATE TABLE分支（branch_id INTEGER PRIMARY KEY，branch_name VARCHAR（50）NOT NULL UNIQUE） CREATE TABLE EmployeeBranches（ee_code CHAR（10）NOT NULL引用 员工（ee_code），branch_id INTEGER非空参考分支 （branch_id），PRIMARY KEY（ee_code，branch_id）） - David Portas SQL Server MVP - You prevent duplication in the EmployeeBranches table as in any other table: with a PK or UNIQUE constraint. However, your constraints have to allow an employee to be added without a corresponding entry in EmployeeBranches, otherwise you could never add new rows to the Employees table. CREATE TABLE Employees (ee_code CHAR(10) PRIMARY KEY, ee_name VARCHAR(50) NOT NULL) CREATE TABLE Branches (branch_id INTEGER PRIMARY KEY, branch_name VARCHAR(50) NOT NULL UNIQUE) CREATE TABLE EmployeeBranches (ee_code CHAR(10) NOT NULL REFERENCES Employees (ee_code), branch_id INTEGER NOT NULL REFERENCES Branches (branch_id), PRIMARY KEY (ee_code, branch_id)) -- David Portas SQL Server MVP --

" David Portas" < RE **************************** @ acm>在消息中写道 新闻：11 ********************* @ c13g2000cwb.googlegro ups ... "David Portas" <RE****************************@acm> wrote in message news:11*********************@c13g2000cwb.googlegro ups... 您可以像在任何其他表中一样阻止EmployeeBranches表中的重复：使用PK或UNIQUE约束。但是，您的约束条件允许在EmployeeBranches中添加没有相应条目的员工，否则您永远不能将新行添加到 Employees表中。 You prevent duplication in the EmployeeBranches table as in any other table: with a PK or UNIQUE constraint. However, your constraints have to allow an employee to be added without a corresponding entry in EmployeeBranches, otherwise you could never add new rows to the Employees table.

这正是我的问题。 我需要确保每个创建的员工在EmployeeBranches表中拥有并维护至少一个 条目。 否则我会让流氓员工无法在任何地方工作 !! 这就是为什么我想在我的员工 表中添加一个Ee_Branch列，该列不是NULL，并且它对应于Branch 表中的条目。这可以用作他们的主要分支位置。我想我/ b $ b然后可以使用我的EmployeeBranches表将它们分配给其他分支， 确保它与它的主分支不同。但是这不是很好的效果。 或者，我可以通过我的网络前端确保创建一个新的 员工，它也会自动在EmployeeBranches表中添加一个条目。 但是如何防止删除这个最初创建的记录，确保每个员工都有分配给它的至少一个分支？ 这变得非常混乱！ 还有其他建议吗？ Dan

This is exactly my problem. I need to ensure that every employee created has and maintains at least one entry in the EmployeeBranches table. Otherwise I''m going to have rogue employees that won''t be able to work anywhere!! This was why I was thinking of adding an Ee_Branch column in my employee table that is not NULLable and have it correspond to an entry in the Branch table. This could be used as their primary branch location. I suppose I could then use my EmployeeBranches table to assign them to other branches, ensuring that it''s different to it''s primary branch. But then this isn''t exactly efficient. Alternatively, I could get my web front end to ensure that on creating a new employee, it automatically adds an entry to the EmployeeBranches table too. But how do I prevent deletion of this initially created record, ensuring that each employee has at least one branch assigned to it?? This is becoming very confusing! Any other suggestions? Dan

Dan Williams写道： Dan Williams wrote: 我需要确保每个创建的员工都维持在最低 EmployeeBranches表中的一个条目。 I need to ensure that every employee created has and maintains at least one entry in the EmployeeBranches table.

我对SQL Server了解不多。我认为它不允许 这样的复杂检查约束？： ALTER TABLE员工添加约束eb_chk 检查（EXISTS （SELECT FROM FROM Employee_Branches eb WHERE eb.EE_Code = e.EE_Code））; 它需要是一个DEFERRED约束。延迟约束是 在你提交之前不会被检查，因此它允许你输入一个新的Employee 行，而不会立即遇到约束违规因为你 还没有插入任何EmployeeBranches行！ 如果这不起作用，另一种方法是使用触发器维持一个 Employees表中的Branch_Count列。我不知道SQL Server 语法，但伪代码类似于： 插入员工： 设置Branch_Count =（从Employee_Branches中选择count（*），其中 EE_Code = ...） 插入EmployeeBranches后的： update员工设置Branch_Count = Branch_Count + 1，其中EE_Code = .... 删除EmployeeBranches后： 更新员工设置Branch_Count = Branch_Count-1，其中EE_Code = .... 那么您需要对员工进行DEFERRED检查约束： check（Branch_Count> 0）。

I don''t know much about SQL Server. I presume it does not permit complex check constraints like this?: ALTER TABLE Employees ADD CONSTRAINT eb_chk CHECK (EXISTS (SELECT NULL FROM Employee_Branches eb WHERE eb.EE_Code = e.EE_Code)); It would need to be a DEFERRED constraint. A deferred constraint is not checked until you commit, so it allows you to enter a new Employee row without hitting a constraint violation right away because you haven''t inserted any EmployeeBranches rows yet! If that doesn''t work, another approach is to use triggers to maintain a Branch_Count column in the Employees table. I don''t know SQL Server syntax, but the pseudocode would be something like: on insert of Employees: set Branch_Count = (select count(*) from Employee_Branches where EE_Code = ...) after insert of EmployeeBranches: update Employees set Branch_Count = Branch_Count+1 where EE_Code = .... after delete of EmployeeBranches: update Employees set Branch_Count = Branch_Count-1 where EE_Code = .... Then you would need a DEFERRED check constraint on Employees: check(Branch_Count > 0).