Can anyone tell me the correct way to store entities in this situation: A department has many member employees and one employee as a head. So:

CREATE TABLE dbo.Department (
    DepartmentId int NOT NULL ,
    HeadedByEmployeeId int NOT NULL ,
    DepartmentName varchar (50) NOT NULL 
)

CREATE TABLE dbo.Employee (
    EmployeeId int NOT NULL ,
    MemberOfDepartmentId int NOT NULL ,
    FirstName varchar (50) NOT NULL ,
    LastName varchar (50) NOT NULL 
) 

Where HeadedByEmployeeId is a foreign key on EmployeeId, and MemberOfDepartmentId on DepartmentId.

LLBLGen fields mapped on relations are renamed as follows:

Department
Head (Department – Employee (m:1))
Member (Department – Employee (1:n))

Employee
DepartmentHeaded (Employee – Department(1:n))
Department (Employee – Department (m:1))

And finally in the code we wish to add a new department with a new member employee who is also its head:

DepartmentEntity department = new DepartmentEntity();
department.Name = "Accounts";
EmployeeEntity employee = new EmployeeEntity();
employee.FirstName = "Tom";
employee.LastName = "Jones";
department.Member.Add(employee);
department.Head = employee;

Now employee.Save(true) fails with ‘Cannot insert the value NULL into column 'MemberOfDepartmentId’’ and department.Save(true) fails with ‘Cannot insert the value NULL into column 'HeadedByEmployeeId'’

Is there any way this can be done without allowing one of the foreign key fields to take nulls?

Thanks, J.

This isn't solvable without having one FK being NULL, because you can't insert a row in either table without having inserted the other one already due to the FK constraint.

One way to solve this is to drop the FK constraint (bad), another to allow null's (which can be bad too, as it will increase the possibility of faulty data in the database.).

A third one is the only right option: add another entity: DepartmentEmployee, as the 'head' attribute is really part of the relation Department - Employee, which then should be objectified. The DepartmentEmployee entity takes care of the relation Department - Employee:

DepartmentID * EmployeeID * IsHead

(* are part of the PK). You then add another Unique Constraint to EmployeeID, making sure an employee can join just 1 department.

Genius. That's much better, thanks so much.

As an aside, would you recommend that this link table, and many-to-many link tables in general, should use the two foreign key fields as a combined primary key, rather than have a separate primary key field with two normal foreign key fields, using a separate constraint to enforce uniqueness of foreign key combination if required? Do you lose anything with either approach?

Cheers, J.

johnsmith wrote:

As an aside, would you recommend that this link table, and many-to-many link tables in general, should use the two foreign key fields as a combined primary key, rather than have a separate primary key field with two normal foreign key fields, using a separate constraint to enforce uniqueness of foreign key combination if required? Do you lose anything with either approach?

As the intermediate entity here is an entity on its own (with a non-PK field 'head'), it can be (in theory) there will be an FK towards this entity's PK somewhere. That will require a compound FK. Some people are not that fond of that and want to use single field PK's in that scenario, which results in a new PK field for the intermediate entity. However I'd leave it as is, as the extra field doesn't add anything because the 2 field PK is already uniquely identifying the entity.

Yes, but how would you do that in a layer of stored procedures?

LOL