How To Create a Complex Table in sqlServer ??

Question

Hi everyone , Lets say I have a table called Employees , and each employee has a primarykey called (E_ID) and I have another table called Positions , and each Position has a primarykey called (P_ID) and I also have another table called offices , and each office has an ID called (O_ID)

Now I want to create a table that has three primaryKeys which are (E_ID) and (P_ID) and (O_ID) ... ofcourse these three values must be withdrawl from the first three tables , but I just can't do it anyway ? please help me because I neeeeeeed it badly thanks verymuch

Answer 1

If it was me, I think I'd just add P_ID and O_ID to Employees. The same Position might be filled by multiple employees, and there might be multiple Employees at a given Office, but it's unlikely (without using Cloning technology) that the same Employee would need to be replicated multiple times - thus, just add P_ID and O_ID to Employee and I think you're good to go. Of course, you'll need foreign key constraints from Employee to Position (P_ID) and Office (O_ID).

EDIT: After some thought, and recalling that I've had jobs where I filled multiple positions (although at the same location), I suppose it's conceivable that a single person might have fill multiple positions which might be at different locations.

If you're really set on having a junction table between Employees, Positions, and Offices - OK, create a table called EmployeePositionOffice (or something like that) which contains the three columns E_ID, P_ID, and O_ID. The primary key should be (E_ID, P_ID, O_ID), and each field should be foreign-keyed to the related base table.

EDIT: Not sure about the SQL Server syntax, but in Oracle the first would be something like:

ALTER TABLE EMPLOYEES
  ADD (P_ID NUMBER REFERENCES POSITIONS(P_ID),
       O_ID NUMBER REFERENCES OFFICES(O_ID));

while the second would be something like

CREATE TABLE EMPLOYEES_POSISTIONS_OFFICES
  (E_ID  NUMBER REFERENCES EMPLOYEES(E_ID),
   P_ID  NUMBER REFERENCES POSITIONS(P_ID),
   O_ID  NUMBER REFERENCES OFFICES(O_ID),
   PRIMARY KEY (E_ID, P_ID, O_ID));

Share and enjoy.