Hey everyone,

So I had a question about how to correctly implement a certain design.

Here's the issue:

I have a table called OWNER which stores basic information about land owners for a system I'm developing. I want to be able to represent the relationship between owners as well. Ultimately, I would like some sort of associative table called OWNER_RELATIONSHIP that allows me to link two owners together, and then have a column that describes their relationship in the new table. I understand how to resolve many-to-many relationships to an associative table between two separate tables, but how do I do it with just one table?

The reason I'm hitting a brick wall is that I want to have a composite key in the associative table that is made up of the primary key for the two owners from the OWNER table. But since the primary key is the same for both owners, I can't figure out how to implement it. Any suggestions would be greatly appreciated.

Regards,

Paul

Attached is what the relationship currently looks like in a logical model diagram...

So a child owner may be related to more than one parent owner via multiple owner relationship types?

I'm not too sure if my diagram is clear, but ultimately I'm just trying to have it where the parent/child relationship is between the OWNER and OWNER_RELATIONSHIP tables, and not between two rows in the OWNER table, if that makes sense... So, I guess the OWNER table is a parent twice for each entry in the OWNER_RELATIONSHIP table...

I loosely understand that he needs a relationship between a couple of tables but no matter how many times I read the requirement I haven't the slightest idea what it is he's trying to model or understand why the simple relationship supplied wasn't enough. Would it be possible for a simple English translation and a couple of examples to show why the current design is insufficient.

Just what is wrong with:

OWNER: id, name, other_data ...
OWNER_RELATIONSHIP: id, child_id, relationship_type

PS You didn't ever work for Goldmans in the 90's and write the spec for their position management system did you?

for one thing, using the word "child"

yes, a table with a FK is a "child" of the "parent" table with the PK

but in this context, those terms are confusling

the diagram posted used the names OwnerId1 and OwnerId2, which i find to be more appropriate, because the nature of the relationship is not "parent"-"child" but owner-owner

it is very clear

I accept child isn't the best wording here but to be fair I can't work out what's wrong with the basic table structure - perhaps you can enlighten us to what the problem is?

me?

structure, let me show u it

Sorry, you seemed to be implying you know knew what the issue was. I was just trying to find out.

Has the restriction been lifted now on advertising merchandise on the forum?

Actively participating members have always been permitted to include their web sites in their forum signatures. While outright advertising (say for automobiles) is always flatly unacceptable, in a case when a new product (book, software, etc) that is directly relevant to DBForums members then it is considered an announcement instead of advertising.

We've had numerous similar situations with other users. At one point in time I considered announcing the release of Joe Celko's books (I derive no reveue from them) in my own sig. The decision is subjective, made jointly by the moderators, and has never been an issue for contention.

-PatP

Hey folks, thanks for the responses. The truth is, I wasn't sure if there was a problem to begin with. I just wanted to make sure that I was modeling it correctly. I didn't know if it violated some rule of database design to reference the same PK from one table as a FK twice in a different table (and then form a new composite PK from the two FKs).

No, referencing the PK from one table twice in another table is no problem. Other than having to rename at least one set of the PK columns, there is no problem at all.

-PatP

Thanks Pat and Rudy! You guys definitely answered my question.