Results 1 to 4 of 4
  1. #1
    bob89 is offline Member
    Join Date
    Feb 2010
    Posts
    2
    Rep Power
    0

    Default Best way to design a simple database

    I am designing a simple sport management game for a project. I have some knowledge of SQL. Enough to design and manage a simple enough database. I am going to be using an Apache Derby DBMS for the project.

    There will be 32 teams each with a panel limit of 30 players. This adds up to just under 1000 players. When a team is playing I will need to load up all players on the team. I was going to have at least two tables starting of. A table for all the teams which would have fields for team name, manager's name and venue etc. Then another one would hold all the players. This would have player bio information and more importantly all there skill attributes which would be dynamic changing with training and matches and time nnd also which team they played for.

    I was wondering if it would be better to structure the tables differently eg Having 30 player slot fields on each team entry which would all be foreign keys pointing to the players primary key.

    I was wondering if this would be more efficient. Would I just have to search through the team table to find the team ID I was looking for and then it would very quickly be able to access all the 30 players using their primary key. Would this be quicker then having to iterate through the 1000 player entries to find only 30 that were needed instead of searching through 32 teams and then having more direct access.

    I am not even sure is what I described possible but if it is would it be a better option and would it be not too difficult.

  2. #2
    PhHein's Avatar
    PhHein is offline Senior Member
    Join Date
    Apr 2009
    Location
    Germany
    Posts
    1,430
    Rep Power
    7

    Default

    I'd keep it simple, as a player can only play for one team.
    Teams:
    team_id
    Name
    ...

    Players:
    player_id
    team_id (FK on Teams)
    name
    skill
    ....

    Whatever you do, with so little data it'll be quick anyway.
    Math problems? Call 1-800-[(10x)(13i)^2]-[sin(xy)/2.362x]
    The Ubiquitous Newbie Tips

  3. #3
    FON
    FON is offline Senior Member
    Join Date
    Dec 2009
    Location
    Belgrade, Serbia
    Posts
    368
    Rep Power
    5

    Default

    Your tables will contain small amount of data so efficiency is not so big issue here.

    Be sure you create proper business rules like:

    Player can participate in zero, one or many TEAMS.

    This gives you one-to-one, one-to-many or maybe many-to-many relationship which can lead you do cross reference table.

    Think about moving players details to another table,
    or live them in players table but use VIEWS to reduce table size
    for operations on it.

    Think about CRUD operations on those tables and their relations.

    Do you need to ever DELETE player. What about TEAM table in that case.

    Perform basic normalization at the end.

    Play around and come up with some db design so we can make more comments.

    cheers!

  4. #4
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    7

    Default

    I was wondering if it would be better to structure the tables differently eg Having 30 player slot fields on each team entry which would all be foreign keys pointing to the players primary key.
    Don't do things like that. It will make your code much harder and it will not improve performance. RDB's are designed to handle data that is fully "normalized" (like, no repeating fields in a row). Google a simple explanation of "database normalization" or "Codd's rules" and apply them.

Similar Threads

  1. Simple DATABASE FROM MSACCESS But ERROR!!!!!!!!
    By Elshan0011 in forum New To Java
    Replies: 2
    Last Post: 12-15-2009, 07:46 PM
  2. Database and user interface design
    By Implode in forum New To Java
    Replies: 12
    Last Post: 09-14-2009, 01:41 PM
  3. New simple application using a simple database
    By webbusiness23 in forum New To Java
    Replies: 9
    Last Post: 08-03-2009, 03:55 AM
  4. database design for leave management system
    By anji4u_19 in forum JDBC
    Replies: 0
    Last Post: 08-26-2008, 12:27 PM
  5. Replies: 2
    Last Post: 05-04-2008, 06:27 PM

Posting Permissions

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