Results 1 to 10 of 10

Thread: Database Design

  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Red face Unanswered: Database Design

    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

  2. #2
    Join Date
    Oct 2011
    Posts
    6

    Red face Attempt

    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
    Last edited by WebNewbie; 10-21-11 at 09:05. Reason: Wrong url

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2011
    Posts
    6
    Quote Originally Posted by r937 View Post
    in the decisions table you have listed a whole bunch of columns, yes? and you've left some out?

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

  5. #5
    Join Date
    Oct 2011
    Posts
    6
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by WebNewbie View Post
    Can you explain why that's a bad idea?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2011
    Posts
    6
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2011
    Posts
    6
    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
    Attached Thumbnails Attached Thumbnails DataBase_Design.jpg   Decisions Table.bmp  

  10. #10
    Join Date
    Oct 2011
    Posts
    3

    Bitwise?

    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.

Posting Permissions

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