Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!elnk-atl-nf1!newsfeed.earthlink.net!bigfeed2.bellsouth.net!bigfeed.bellsouth.net!news.bellsouth.net!peer01.cox.net!cox.net!small1.nntp.aus1.giganews.com!nntp2.aus1.giganews.com!intern1.nntp.aus1.giganews.com!nntp.giganews.com!news.giganews.com.POSTED!not-for-mail NNTP-Posting-Date: Thu, 27 Nov 2003 05:39:08 -0600 From: "David Portas" Newsgroups: comp.databases,comp.databases.ms-sqlserver,comp.databases.theory,microsoft.public.sqlserver.programming References: Subject: Re: DBDesign Q2: Date: Thu, 27 Nov 2003 11:41:37 -0000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: Lines: 23 X-Trace: sv3-S93Y5BIxBEEn5bEnU7EMeBCHW1niCnZRLVPl0fPNerEqsyf609A1w8QFXFtBuerVVFKdlcfSHWsZYB2!HG0SmaAdiu9CFx9OIHlo6zZiSephn1J+xyk7/QTXz2V5unPzThEZT5DgazDmhSU= X-Complaints-To: abuse@giganews.com X-DMCA-Notifications: http://www.giganews.com/info/dmca.html X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 Xref: newssvr20.news.prodigy.com comp.databases:48760 comp.databases.ms-sqlserver:105901 comp.databases.theory:22863 microsoft.public.sqlserver.programming:308743 The following seems to match your business rules. Treat Department Manager as an attribute of the Department rather than the Employee. CREATE SCHEMA AUTHORIZATION dbo CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL REFERENCES Departments (deptno), UNIQUE (deptno, empno)) CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20) NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno, deptmanager_empno) REFERENCES Employees (deptno, empno)) The usual caveat about design questions applies: it's very difficult to give design advice online without the opportunity to research a particular situation in detail. -- David Portas ------------ Please reply only to the newsgroup --