Hello;

I'm trying to design a database to keep track of some clubs and owners
One owner can have many clubs.
One club can have many owners (partners).
Each partner has seperate enry in the Owner table.
Now my problem arises when clubs are sold I need to keep track of the previous owner/club combination.

Right now I have.
Club: ClubNr (PK) <<other fields>>
Owner: OwnerNr (PK) <<other fields>>
ClubOwner (LinkTable):
ClubNr (FK) OwnerNr (FK)
Club1 Owner1
Club1 Owner2
Club2 Owner1
Club3 Onwer3

I thought about putting a ClubOwnerNr as PK in the LinkTable, but the problem here is,
that it would not contain unique values if club has multiple owners. (eg Club1)

I have many other tables that would use this "ClubOwnerNr" as their FK.
Any suggestions?

Thanks,

Kurt

The correct PK for ClubOwner is the composite key (ClubNr, OwnerNr). This can also be a composite FK in other tables.

If you don't like composite foreign keys then by all means add a surrogate key ClubOwnerNr and make it the PK - but then you must also define (ClubNr, OwnerNr) as a UNIQUE constraint.

I was thinking about this but if I use the composite key (ClubNr, OwnerNr). I'll have a lot or redundant data in my other tables Tables due to clubs with multiple owners.

Thanks again,

Kurt

If these other tables depend only on the Club, and not on the Club/Owner pair, then their foreign keys should reference the Club table, not the ClubOwner table!

Hi Tony;

The problem with doing it based on just the ClubNr, is that if owners change. I'll loose the info on the previous Club/Ower Data.
I need to keep old info too, and when a club is resold the ClubNr does not change.
I can't put it with the OwnerNr, because if the owner has multiple clubs I won't know which club this is.

I don't know but this is driving me nuts,,,

Thnx again,

Kurt

How would you lose that information? Surely it will be in the ClubOwner table still? Probably with some dates to be useful:
What sort of information do your other tables contain? Does it relate to the Club per se (regardless of who owns it) or to the Club only while owned by a particular Owner? The answer to that question (for each table) determines whether the parent should be Club or ClubOwner. The fact that you have said that using ClubOwner means redundantly duplicating information suggests that the answer is parent=Club and not parent=ClubOwner.

For example, suppose there is a table of Members (seems reasonable?) Since presumably members don't all leave the club whenever it changes ownership, they belong to the Club, not the ClubOwner: