Normalization: Two coloumns in a single row require data from the same table.

Question

I have a table which has the following fields:

Assignedto and completedby

These fields reference the employee table.

I was just wondering if this is normalized or whether I have missed something.

Thanks

Answer 1

Yes, it is fine (and appropriate) to have more than one column in a table be a Foreign Key into another table. Your case is a good example of this...

There are lots of examples actually - A Customer Table can have a mailing address and a Delivery address, which both point to an Address table.. A Contact Table could have a HomePhone, WorkPhone, Fax, and Mobile column that all point to a Telephone table (although there are other better ways of doing this one)

Answer 2

Note: I'll refer to your second table as "Stuff" since I don't know what else to call it.

It's 3NF if the AssignedTo and CompletedBy fields are both foreign keys to the Employee table. If you really want to go nuts with the normalization, you can have a third table, StuffCompletion, that has an identity column and those two values, and replace those two values in your Stuff table with a foreign key to StuffCompletion. I think most people would consider this overkill, and the schema gods may rain destruction upon you.