I am having trouble getting my head around the best way to design my database for a simple online game.
Here are the details followed by my attempt and questions:
A game where multiple decisions are made by teams competing against each other.
Decisions are grouped into 7 stages where each stage has multiple choice actions. Based on their decisions, their performance is calculated and outputted after each stage of decisions.
At the end of the game the teams are ranked in terms of performance.
Firstly I have drawn up some inputs that I will obtain from the users:
Table Name: Game
Total No. of Players
No. of Teams
Table Namechool Details
Table Name: Team
No. of Players in Team
Table Name: Decisions
Marketing Research T
Head of Engineering
Head of S&M
Machinery Choice 1
Number of Machines 1
Product Research & Development Budget 1
Sales and Marketing Budget 1
Advertising Budget 1
Production Run 1
Table Name: Summary
(Linked to TeamID)
Current Market Share
Previous Market Share
I have grouped these into what I think the tables should be. There is a possibility of a game running from different schools with the games being unrelated and running separately hence the GameID.
A classroom is split into teams of about 5 people per team. Then they choose a team name, and begin to play the game together. They type in their TeamName and Password their logo is uploaded and they input their slogan. Next stage they make decisions via radio inputs. eg(They select their Martketing Team from one of three radio buttons, then they choose their Head of Engineering from one of three radio buttons). After some choices are made they input their price for the product and the details are sent to the database. Then they must wait until all other teams have completed stage1 and then the game manager decides when access is available to stage 2. In stage 2 and other stages they input new values and may update old ones. They are shown a report of their current financial state at the start of each new stage.
Sorry for the long message but I am unsure how to divide up the "Decisions" table for it to run more smoothly.
in the decisions table you have listed a whole bunch of columns, yes? and you've left some out?
this is obviously a weak point in the design
there should only be one column (or maybe two or three) for all decisions
what you have as columns, should be rows, where the name of the decision is one column, and the choice for the decision is another column -- this is why there might be a few columns, one might be yes/no, another might be an amount or percentage, etc.
Ok, I've done that but I'm wondering how do you relate the decisions table to the Team ID?
By having the decisions in columns (hence having lots of columns) I can make the teamID as a foreign key and it will be a row with all the inputs of the user...?? Can you explain why that's a bad idea?