Hi, I am new to mySQL database design.

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
Game No.
Total No. of Players
Current Status
No. of Teams

Table Namechool Details
SchoolName
ContactName
Email
Phone
Street Address
City/Town
County

Table Name: Team
Team ID
TeamName
Password
Logo
Slogan
No. of Players in Team

Table Name: Decisions
Marketing Research T
Investment
Sector Choice
Start-up Consultant
Sale Price
Casing
Heating Element
Fan
Switch/Controls
Packaging
Head of Engineering
Head of S&M
Factory Size
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 Balance
Previos Balance
Current Market Share
Previous Market Share
Previous Price
Current Price

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.

Thank you for your time
WebNewbie

Here is a drawing of my database design...there are a lot more attributes in the decisions box I never put in but you get the idea.

I am wondering if this is correct also how could I improve it? I would like to break down the decisions into the stages as mentioned in the above post.

http://i55.tinypic.com/2wmdvky.jpg

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.

Yes you're right ill give it a go and see if I understand what you mean.
Thanks for the help really appreciate it

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?

Thanks again, sorry I'm only learning this stuff.

why what's a bad idea? the foreign key?

it isn't a bad idea at all, it is still needed

why are multiple similar columns all named differently a bad idea? because you will forever be adding/modifying columns, changing the table design

Ok what I dont understand is how the TeamID will relate.

There will be multiple Teams playing the game at the same time all competing against each other meaning I will have several values (sometimes might even be the same values) for each decision.

And so I would have thought this solution: http://i56.tinypic.com/whd5yu.jpg
Would have better suited as each team has a row of its decisions??? With TeamID being the foreign key

Obviously the columns would extend out as I have left out alot of the decisions for handiness.

you seem to be really tied to your design

so why not just go ahead and implement it?

by the way, the tinypic site is down -- you know you can just attach an image to your posts here, right?

OK Well I just wanted to know if I could improve it but also understand how it is improved and why it is better

Again thanks for your input I have attached my images

Hey there, thought my 2 cents might be usefull. Have you looked into Bitwise operations, If your using php, this can be a super fast way to control Access/Routing.

There are some really good tutorials on the subject out there.

Don't know if that is usefull or not, just thought i would post it.