Database design help

[dink87522]

Okay, not sure what exactly I need but I need to store data in a MySQL database.

 

The financial data for each user has to be stored for each day.

 

I.e. User 1 has 20 financial data columns say (rent, maintenance, wages, shopping... etc). I need to store each of those separately so they can be displayed in a report.

 

There is more than one user. Most likely a couple of hundred users although it needs to work for up to a few thousand users.

 

Also, each user's daily financial data has to be stored against the date of that data so the user can see the change in finances between day X and day Y for example.

 

So far my best idea has been to create a table for each user and then in that table store the financial data in the columns against the date as the primary key. Does anyone have a better way for this or should I do this?

[dink87522]

My other thought is to have a table for each day (maximum time a day needs to be stored for would be 6 months, so 180 tables) and then in each table have the primary key as the users name, then the financial data stored against that.

 

Do you think this would be better?

 

The data will be used to generate individual user reports only (i.e. so a user can look back at their financial history).

[harristweed]

I would have three tables:

1. User Details

2. Headings (rent, maintenance, wages, shopping etc) two columns heading_id and heading_description

3. Transactions. Four columns: trans_id, Trans_date, heading_id, amount

[laffin]

#3 should have 5 columns, userid as well

[harristweed]

DOH!

[dink87522]

$query = "SELECT * FROM Transactions, Transactions, Users WHERE Transactions.userID = '6' AND transDate = '2010-12-09'";
$result = mysql_query($query) or die("Error: <br>" . mysql_error());
$count = mysql_num_rows($result);
echo ("Count: $count <p>");

if ($count >= 1){ 
echo "<table class='with-border highlight' cellpadding='5'>";
echo "<tr class='row-1'> <th>Heading</th> <th>Amount ($)</th> </tr>";

while(($row = mysql_fetch_array($result))){ // Keeps getting the next row until there are no more to get
	// Print out the contents of each row into a table
	echo "<tr class='row-2'><td>"; 
	echo $row['heading_description'];
	echo "</td><td>"; 
	echo $row['amount'];
	echo "</td></tr>"; 
} 
}

echo("</table><p>");

 

Thanks for that, I've tried to use it above and are running into problems. The table setup is as you described above.

 

I've got some test data in the tables. Using the above query I always gets 3x the correct number of rows I should be getting, what is wrong with my query (I want to display in a table the heading(s) with the corresponding amount)?

[dink87522]

?

[dink87522]

Can anyone see the problem with the query?

[harristweed]

You are only retrieving data from one table:

$query = "SELECT * FROM Transactions, Transactions, Users WHERE Transactions.userID = '6' AND transDate = '2010-12-09'";

If you have three connected tables you need to 'join' them correctly.

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[dink87522]

You are only retrieving data from one table:

$query = "SELECT * FROM Transactions, Transactions, Users WHERE Transactions.userID = '6' AND transDate = '2010-12-09'";

If you have three connected tables you need to 'join' them correctly.

http://www.tizag.com/mysqlTutorial/mysqljoins.php

 

Okay, I actually had read that page before lol although after reading it again do not still understand it I don't think. Tizag tutorials are normally excellent although I found other websites more clearer in this case.

 

I got it owrking using

$query = "SELECT * FROM playerFinanceTransactions, playerFinanceCategories, Users WHERE playerFinanceTransactions.categoryID = playerFinanceCategories.categoryID AND playerFinanceTransactions.userID = Users.userID AND Users.userID = '8' AND playerFinanceTransactions.categoryID = '3'";

 

Thanks for your help!