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.

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.

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!

Quote:

---

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.