We have an ADDRESS table which links to lots of different parent tables (like employer or applicant) by using the combination of a KEY field and another field which designates the table being linked.

For example: ADDRESS TABLE - Type field = values could be "EMPR" or "APPL" - Key field = primary key on either employer table or applicant table This is a simplification, but gets the basic concept.

Within LLBLGen, I can create a relationship between EMPLOYER and ADDRESS using the KEY1 field. However, what I really want is to specify also that the TYPE must be "EMPR". Can this be done?

If I don't specify type then if I have primary key for employer of 1012 (for example) and also have a primary key of applicant of 1012 - then the relation would show me addresses which belong to both employer and applicant (something I don't want to happen).

Any ideas - besides the obious which is to create a key field in the address table for each and every table which is needing to link to it?

Thanks, -Dave

There is more than one solution.

Database Design solution: Create intermediate tables which inherit from the Address table.

Employer

EmpId (PK)

EmployerAddress

AddressId (PK) And FK to the Address table EmpId

Address

AddressId

By the same manner you should have ApplicantAddress table and so on. And this could be levaraged by LLBLGen Pro inheritance feature.

Cons: too much work in the databsae.

Code Solution: Pass a filter to be used with the relation. Using the EntityRelation.CustomFilter property. This also can be hardcoded in the generated code (RelationClasses).