Universe Design - Linking two columns in a table to a subordinate table

Post Author: bradwist

CA Forum: Semantic Layer and Data Connectivity

I hope this is the right forum to ask a question about Universe Design. If not, please redirect me. Thanks.

I have a question on a universe design that I am trying to implement. I'll try to simplify to what I really am focusing on here. I've got a table, PersonRelationship, that defines a relationship between two people (Person1, Person2). Both Person entities have a set of data that are contained in the PersonTable. I'm trying to set up a universe design that allows the PersonRelationship table to join to the Person table twice, for Person1 and Person2 (fields are Person1Id and Person2Id respectively).

(below is a poor attempt to draw the table relationships)

Person (table) PersonRelationship (Table) OtherData (table)

-

-

-

PersonId (PK) RelationshipId (PK) OtherDataId (PK)

^----

Person1Id (FK)

^----

Person2Id (FK)

OtherDataId (FK) -

^

I then would like to build a query in Web-I that allows me to query against certain parameters for both Person1 and Person2. For instance, I may want information that reflects when Person1 is male and Person2 is female.

Is there a way to establish this relationship within the universe without having to create two physical tables?

Thanks.