Tables vs columns

[SurSokK]

Hi.

I'm quite new to php/sql, but I'm quite ambitious:)

 

The thing I need to figure out first is how i should organize my database and tables.

 

My project is this:

We have started a sort of leage at my local laser tag arena. The battles are 1on1 and each battle consist of two rounds.

I want to create a webpage where I can enter the data from these matches and people can view the information in any way the want (when it comes to the search criterias)

So far, so good. For this I only need som simple INSERT, CREATE, UPDATE, SELECT and others commands like that I believe.

 

The main thing I'm wondering is how many tables i should create.

 

Data from the matches will be:

The date of the match

Player 1 (name from list), life left round 1 (number), laserpack number round 2 (from list), life left round 2, laserpack number round 2, totalt life left, victory (yes/no)

Player 2 (same as player 1)

 

I recon I need one table for the players, but for the matches there's quite a few alternatives.

 

1. Each match use one row consisting of  15 columns (to contain all the date from the match as described)

2. Each match creates a new table. In that table there will be to rows, one for each round of the match

There's probably more ways to organize this, and thats where you, people with coding experience from a higher level of consciousness than me, come in

 

When people look at the webpage I want the matches to be listed with the result only (total lives left and who the winner was), but they shoud be able to click into each match to view details of each round in that match.

 

I don't know if i described my question good enough, so feel free to ask for details

If any of you know about some tutorials that would help me creating this project i would really love a link. I rather like to read things and tweak the code to my likings than just get handed the exact code i need (thats the only way to learn)

[Muddy_Funster]

Work with more tables, rather than more records. 

 

Also - make sure you index properly and use relevent table & field names, especialy for keys.

If you are going to be generating the tables from the code as and when then you will want to have another table to index the table names ith other relevant information, such as creation date.

 

...

I rather like to read things and tweak the code to my likings than just get handed the exact code i need (thats the only way to learn)

 

Actualy - I hear taking classes works as well 

[SurSokK]

Actualy - I hear taking classes works as well 

I prefer the free classes I get from the allmighty internet

 

Thanks for quick answer. Now I know where (and how) to start building the database. Now I just need to learn all the code

Currently I'm reading about PHP and MySQL at www.w3schools.com. Do you have any other sites for noobes? Altough I don't like it, I realize I need to learn all the boring basics first

[Muddy_Funster]

the one sight that you will be unable to live without for php is the manual site:

http://php.net/manual/en/index.php

 

mySQL also has one but it's kinda suckie compared to php's

http://dev.mysql.com/doc/refman/5.1/en/

 

There is of cource a wealth of information here at phpFreaks as well, although finding it can some times be a bit tricky.  I know a few of the more knowledgable types have a couple of guides floating about, and there are a few that would even make you one up if you ask nice enough (not me though, I suck at this stuff  )

[ignace]

Can you give us a description of a regular game? How does it start? How is it played? .. Otherwise the below may work for you:

 

CREATE TABLE Player      ( player_id  MEDIUMINT   UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY );
CREATE TABLE Match       ( match_id   MEDIUMINT   UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                           winner     VARCHAR(16)          NOT NULL );
CREATE TABLE MatchPlayer ( player_id  MEDIUMINT   UNSIGNED NOT NULL,
                           match_id   MEDIUMINT   UNSIGNED NOT NULL,
                           health     TINYINT              NOT NULL,
                           laser_pack TINYINT     UNSIGNED NOT NULL,
                           PRIMARY KEY (player_id, match_id) );

[SurSokK]

I'm not sure your example would cover enough data.

A match consist of two rounds. Each player start with 20 lives (health). When one player is out of lifes the round is over and we write down the number of lifes left on the other player. An example of a match would be:

 

Date

Round 1

SurSokK, Green 10 (laser pack), 8 (lives left)

Adversary, Red 15, 0

Round 2

SurSokK, Red 15, 0

Adversary, Green 10, 5

Result

SurSokK lost 32 lives out of 40

Adversary lost 35 lives out of 40

SurSokK wins with 3 lives

 

At the webpage i want people to be able to list all matches played on a certain date or within a period (from date to date). There, only the result of the match (Player 1 vs Player 2, winner is * with * lives left), and when they click on a match they get all the info about that match.

 

A history of a certain player vs another player would be cool also And of course a detailed history of each player and each laser pack (such as total life lost out of total lives, how many games/rounds played and so on).

And then there is the ranking system... I think i will just update the ranking list manually, but i would like to save the current ranking of each player after each match so progress throuout the year can be shown (prefebly in a diagram)

 

basicly i want to be able to make a search based on pretty much any criteria vs any criteria.

 

That's why my orginal question was about what is better: many records in one table, or many tables.

For me, a beginner at php and sql, it seems the code needed to do a search and display data based on multible tables would be a bit advanced.

 

So my next question is: Should I wait to open the webpage for action until all the coding is ready, or can I start off with something simple as just listing all the matches and build the search functions bit by bit. I recon the structure of the database must be decided and completed from the start?

 

And don't cunfuse this with questions about webdesing (I will probably come back to that ). For now I only wonder about the structure of the database.