View Full Version : Need a way to organized my project.

02-01-2012, 06:11 AM
Some background info:
This application is a tracking tool to monitor high scores of a game and keep track of user progress, etc.

An example of a record in the database:
id | username | json_encode(array('atk'=>99, 'str'=>99, 'def'=>99))

The json object could have 20-24 key/value pairs. I'll need to be able to do something like

select * from records where id = 10, which would be "user1's" stats at some point in time.

then select another row which is user1's stats like 72 hours later, then perform calculations on each piece of data in the json decoded array. someone recommended creating a table for each user, but is this practical/efficient?

02-01-2012, 06:32 AM
Why have the json encoded array in the database? Why not just have the user's attributes inside the table in different values? OR, a separate table that has an ID foreign key linking to the user key which lists all attributes at certain tables - my recommendation.

So the users table would have field names id and username, the stats table would have field names user_id (links to user.id), a field for every stat and a field for a timestamp (the time that stat took effect).

A table for every user is a bad idea for so many reasons.