Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005

    Unanswered: Circular Database


    I have created the following 2 tables and one person is commented that these are circular type and it won't work. Both tables having foreign keys of other table. I have argued that, there is no problem that we can update the column values at later time (Ex: CaptainId & WicketKeeperId).

    TeamId - Primary Key
    CaptainId (foreign key from players table)
    WicketKeeperId (foreign key from players table)

    PlayerId - Primary Key
    TeamId (foreign key from CricketTeams table)

    Can any one comment whether my argument is correct or not. I hope there won't be any impact on the database performace also.


  2. #2
    Join Date
    Jul 2003
    you could have problems with your code logic if you don't do things in the
    correct order.

    Think of a scenario:
    1. What if a team drops out? You cannot drop the team from the table without first reassigning all the players to different teams.
    2. What if a player who is a captain or keeper (or both) drops out of the league? You would first need to assign e NEW Keeper/Captain before you delete the Player from the PLayers table.
    3. Can you have 2 Keepers? Can you have 2 Captiains?
    4. Can you be a keeper for more than one team?
    5. What if a normal player changes teams and becomes a captain for a different team?? Now we are getting complicated here.

    Basically it could get confusing as to what table you need to update first.
    Run some test-code to determine problems. Perhaps you need an intermediate table between the two. Look up the 5 rules of normalization.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2005
    Thanks for your detailed information. Offcourse, I will agree that, If we want to drop the row in parent table, the related child table rows also will be deleted. My intension is in CricketTeam table will have all the rows of team except the foreign key column values. After that when we have players in the Players table with those values, we can update the rows in the CricketTeam table. Whenever there is a change of a Captain or WicketKeeper, we have to modify the values in CricketTeam table. I will agree that we can create a separate table with TeamId,CaptainId and WicketKeeperId.



  4. #4
    Join Date
    Jun 2004
    Liverpool, NY USA
    If it was my design, I would have the following

    Team table

    Player Table

    Members table

    Position Lookup table

    The members table would contain the following

    Team_id, Player_id, Position_id

    The members table would foreign key to members, teams, and position.

    The table design gives total flexability to the task.
    Last edited by beilstwh; 06-19-06 at 16:09.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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