Heyaz,

I'm looking at a couple options for modeling entities in my data model. #1 Use sub-categories, #2 Entities standalone. There's a best practice of not mapping a non-PK attribute to more than one table... how does this apply in the following situation?

1 sub-categories...

Party (entity base) ... id ... type ... name ... Person (sub-category) ...... lastname ...... firstname ...... DOB ... Organization (sub-category) ....... name

Address (FK Party.id) ... id PK ... PersonId FK, AK1 ... addressType FK, AK1

... addressLines

So, in #1 Address is mapped to Party and "inherited" by sub-categories. Not a problem as I understand it.

2 standalone entities...

Person ... id PK ... lastname ... firstname ... DOB

Organization ... id PK ... name

Address (FK Party.id) ... id PK ... EntityId FK (Person or Organization) ... addressType FK ... addressLines

In #2 both Person and Organization are FKs to Address. Address has a surrogate key. EntityId and addressType are AKs/constrained unique. This is a problem as I understand it.

Does this work? Must I make the PK real (as in EntityId+addresstype)?

Thoughts?

Best, /jhd

In scenario 2 there is no "requirement" to make a composite primary key. I would atleast consider a unique constraint on the 2 fields, that way in your Organization and Person controller you can create a FetchUsingUniqueConstraint. In the organization controller, pass the organization type and id of the organization, and in hte person controller pass the person id and person type id.

This should fetch addresses for organizations as well as fetch addresses for people. I also beleive that prefetch paths would help you out (maybe) and I am also fairly sure that you can add relations at runtime without a physical relation existing (but Frans) should confirm this.

Devildog wrote:

In scenario 2 there is no "requirement" to make a composite primary key.

From the DB perspective, llblgen perspective, or both?

DB best practices says something about not defining more than one FK to a non-PK field. In #2 [I thought] that would mean dropping the surrogate uid PK and going with EntityId+AddressType (or whatever).?.?

Devildog wrote:

I would atleast consider a unique constraint on the 2 fields, that way in your Organization and Person controller you can create a FetchUsingUniqueConstraint.

Yeah, took me a while to figure out how to do this in Visio... it's really buried and generates duplicate name for index and constraint when you create it

Devildog wrote:

In the organization controller, pass the organization type and id of the organization, and in hte person controller pass the person id and person type id.

Good stuff. Will dig into controllers

Devildog wrote:

I also beleive that prefetch paths would help you out (maybe) and I am also fairly sure that you can add relations at runtime without a physical relation existing (but Frans) should confirm this.

Will look into runtime relations... configuring the entity (Person, Organization) at runtime might really clean it up.

Appreciate it.

Best, /jhd