mySQL database design help

[doczaius]

I'm working on a project right now that will require a rather large database, but having trouble figuring out the best way to set it up.  I've used mySQL before but just to store simple web data.

If this were an excel workbook I would set it up like this:

Sheet1:

#ID  NAME NUMERIC_DATA1 NUMERIC DATA2.
1    whatever  454264  33
2    somthing else  232455  67
etc.

Sheet2:

#ID  DATA_VALUE@TIMESTAMP  DATA_VALUE@TIMESTAMP
1  454264 476547
2  232455 300120 


Sheet 1 would update periodically when there are new users.  Sheet 2 updates every three hours, every day of the year.  So in the example above I would append new columns to the end for every three hours.  Note that there are 40,000+ existing users so as I said before this is going to be fairly large.

I suppose the question is how many tables should I use, and how exactly would I setup the fields, reference them in PHP etc.

I know this is pretty vague but hopefully someone can make sense of it, if not just let me know what other info you need.

[c4onastick]

It really depends on what sorts of queries you're going to be running on the data. If NUMERIC_DATA1 and NUMERIC_DATA2 change over time, or have some other special relation to the name (category perhaps?) then you may want to separate those out into a separate table using foreign keys. Tell us a little about the data, what kinds of things are you going to be looking for in it?

Basic stuff like this:[code]SELECT name FROM Sheet1 WHERE numeric_data1 = X;[/code]
Or are you doing something like:[code]SELECT
  a.name AS name,
  a.id AS id
FROM
  Sheet1 AS a,
  Sheet2 AS b
WHERE
  a.numeric_data1 = b.data_value@timestamp1
  AND
  a.numeric_data2 = b.data_value@timestamp2;[/code]

And I assume you mean append [b]rows[/b] to the end of Sheet2 every 3 hours. Right?

[doczaius]

Maybe this will help.  This is a table that basically contains all of the data that is provided every three hours:

[table]
[tr][td]ID[/td][td]NAME[/td][td]SCORE[/td][td]NUM_OF_GAMES[/td][/tr]
[tr][td]34213[/td][td]A Team[/td][td]99423[/td][td]99[/td][/tr]
[tr][td]45343[/td][td]Team America[/td][td]87323[/td][td]94[/td][/tr]
[tr][td]67434[/td][td]The New Guys[/td][td]5432[/td][td]11[/td][/tr]
[/table]

In the above, the ID and the NAME will never change, but there may be new additions.  NUM_OF_GAMES might update every three hours, whatever value is present can be overwritten, ie it does not need to be tracked.  I do need the SCORE however to be recorded for every update.  So every three hours I need the time/date recorded and the current SCORE for each ID. 

Basically this information will be referenced for charts/graphs and predictions, ie total progress over past week, month, quarter etc. So I might run a query for all the scores for ID 34212, and 67434 over the past week so that I can calculate the average say 24 hour pace for each team, and make a prediction as to when team 67434 might, if ever, overtake team 34212.

[SweetLou]

It looks to me as though you would need just the two tables, the way you have it now. The updating can be done through PHP.

[doczaius]

Ok, I'm working on populating the data for the main table right now.  All of the data is stored in a multidimensional array in php.  I am using a for loop to insert the data into the mySQL table but every time I execute the script only 215 records are populated.  Whats odd is that they aren't even the first 215 values.

[code]
// Begin MySQL transaction
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "I wasn't able to select the database $database. Double check that it exists!");

for($i = 0; $i < sizeof($teamarray); $i++) {
$query = "INSERT INTO 2007_db VALUES (".$teamarray[$i][0].",".$teamarray[$i][1].",".$teamarray[$i][2].",".$i.")"; // Last column is to track which pass of the array is being entered into sql table
echo("$i<BR>"); // Tracks the amount of cycles through loop
mysql_query($query);
}
mysql_close();
// End MySQL transaction
[/code]


Here are the first ten records of the table after the script is run.

[quote][table][tr][td]team_id[/td] [td]team_name[/td] [td]team_score[/td] [td]index[/td][/tr]
[tr][td]10002[/td] [td]10002 [/td][td]4514710 [/td][td]168[/td][/tr]
[tr][td]22000 [/td][td]junkteam14042 [/td][td]2034886 [/td][td]292[/td][/tr]
[tr][td]32049 [/td][td]993911 [/td][td]550173 [/td][td]783[/td][/tr]
[tr][td]44365 [/td][td]950560 [/td][td]347926 [/td][td]1095[/td][/tr]
[tr][td]22204 [/td][td]junkteam14104 [/td][td]234345 [/td][td]1497[/td][/tr]
[tr][td]45663 [/td][td][table][tr][td]6767777 [/td][/tr][/table][/td][td]127010 [/td][td]2369[/td][/tr]
[tr][td]22010 [/td][td]junkteam14046 [/td][td]81118 [/td][td]3174[/td][/tr]
[tr][td]41096 [/td][td]777777 [/td][td]76050 [/td][td]3304[/td][/tr][/table][/quote]

Here is the output in firefox:
[quote]
0
1
2
3
4
5
6
7
8
9
10...17573[/quote]





Any ideas?

[c4onastick]

How did you set up the table? What did you use to define the column data types, i.e. int, float(N,M), etc.? Obviously the PHP is working, since you're getting the "echo $i;" output. I'll bet it's on the MySQL side. Add this:
[code]$db = mysql_connect(localhost,$username,$password);
mysql_select_db($database, $db) or die( "Database connect error.");

for($i = 0; $i < count($teamarray); $i++) {
  $query = "INSERT INTO 2007_db VALUES (".$teamarray[$i][0].",".$teamarray[$i][1].",".$teamarray[$i][2].",".$i.")";
  echo("$i<BR>");
  mysql_query($query);
  if(mysql_errno($db)) { echo mysql_error($db); }
}
mysql_close($db);[/code]
See if that gives you any meaningful error notifications. I've got a sneaking suspicion that the error has something to do with multiple primary keys because of size limitations in your ID field (the same thing happened to me once, took forever to find the culprit).

[doczaius]

Any would be an understatement lol.

Getting stuff like:

[quote]Unknown column 'teamxio' in 'field list'1500
You have an error in your SQL syntax near 'Source Clan,233703,1500)' at line 11501
You have an error in your SQL syntax near 'Reno,21503,6429)' at line 16430
You have an error in your SQL syntax near 'Gurus,21496,6430)' at line 16431
You have an error in your SQL syntax near 'Martini,21489,6431)' at line 16432[/quote]

I think mySQL is trying to interpret the data in the teamarray[$i][1] variables. 

I have the table setup as

team_id      mediumint primary key
team_name  varchar
team_score  int
index          mediumint

[c4onastick]

[quote author=doczaius link=topic=122283.msg504808#msg504808 date=1168813465]
Any would be an understatement lol.
[/quote]Right you are!

Looks like they're not getting quoted right.
[code]$query = "INSERT INTO 2007_db VALUES ('$teamarray[$i][0]', '$teamarray[$i][1]', '$teamarray[$i][2]', '$i')";[/code]
I'm skeptical that will be the end of your problems, I usually use PEAR:DB which handles all of the quoting and escaping and whatnot for you, but its a start!

[doczaius]

BINGO! 21618 loops and no errors.  The table is full of correct data.

I did have to enclose the array variables in brackets though. First run I ended up with a bunch of duplicate entries of "Array".

[code]$query = "INSERT INTO 2007_db VALUES ('{$teamarray[$i][0]}', '{$teamarray[$i][1]}', '{$teamarray[$i][2]}', '$i')";[/code]

I definitely will have to look into using PEAR, especially since this is going to get sickly more complex once I start doing calculations.

[doczaius]

Thanks for everyones help before. 

Heres something new thats racking my mind. I'm working on the script that runs and updates the 2nd table every few hours. I have already written code to check for new teams, but the following code that updates any new and existing records with the scores just is not cooperating:

[code]$query = "UPDATE 2007_db SET $timestamp = '{$teamarray[$i][3]}' WHERE team_id = '{$teamarray[$i][0]}'";[/code]

This is returning:
[quote] You have an error in your SQL syntax near '1168922152 = '11629377' WHERE team_id = '0'' at line 1[/quote]

$timestamp is referring to a previously added field to the table using ALTER. I've tried every conceivable form of single, double quoting and concatenation hoping that SQL is trying to interpret the data again like before but nothing works.  I'm sure its super simple but I will leave it to you geniuses to tell me that.

[c4onastick]

[quote author=doczaius link=topic=122283.msg505817#msg505817 date=1168922243]
$timestamp is referring to a previously added field to the table using ALTER. I've tried every conceivable form of single, double quoting and concatenation hoping that SQL is trying to interpret the data again like before but nothing works.  I'm sure its super simple but I will leave it to you geniuses to tell me that.
[/quote]Why do you have a column named '1168922152'? I think it should read:
[code]$query = "UPDATE 2007_db SET time_stamp = '{$teamarray[$i][3]}' WHERE team_id = '{$teamarray[$i][0]}'";[/code]As a column name, not a variable (unless the column name is stored in the variable as a simple string).