Multiple FK mappings to non-key attribute

Posts   
 
    
Dhominator avatar
Dhominator
User
Posts: 16
Joined: 28-Dec-2004
# Posted on: 30-Dec-2004 00:45:38   

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

Devildog74
User
Posts: 719
Joined: 04-Feb-2004
# Posted on: 30-Dec-2004 05:18:39   

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.

Dhominator avatar
Dhominator
User
Posts: 16
Joined: 28-Dec-2004
# Posted on: 30-Dec-2004 17:01:41   

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 rage

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 sunglasses

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