Results 1 to 6 of 6

Thread: Link table

  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Link table

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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!

  5. #5
    Join Date
    Dec 2005
    Posts
    3
    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

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    How would you lose that information? Surely it will be in the ClubOwner table still? Probably with some dates to be useful:
    Code:
    create table ClubOwner
    ( ClubNr references Club
    , OwnerNr references Owner
    , StartDate date
    , EndDate date
    , ...);
    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:
    Code:
    create table Members
    ( ClubNr references Club
    , MemberNr, StartDate, EndDate, ...
    , primary key (ClubNr, MemberNr)
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •