1

Hello I am trying to figure out the best way to setup my two mysql tables for a simple points system?

What I have:

1 - users table with id (unique),name,email,etc
1 - points table with id (auto incrementing),user_id (corresponds with id field in user table), points

Currently I am just writing to the points table and letting the id field auto increment... and setting the user_id field and points fields with php in the query.

Is this the "proper" way to set this up? Where the id from the user table is common with the user_id field in the points table? Or is there a better way?

I feel like maybe I should just have two fields in my points table (id,points) and then have the id's correspond? Just not sure what's best? What I should be doing here?

I just want to be able to have a function or two that let me update the points field for a user... or read it... etc?

Thanks for helping!

DigitalMediaGuy
  • 421
  • 1
  • 5
  • 18
  • 1
    There's not enough information to answer definitively, so just be aware that you need to establish a primary key on each of the tables. If you think that can be formed 'naturally' (i.e. on some combination of existing columns) then fine, otherwise you will need to provide a surrogate key (your 'id' column) to act as the primary key. That said, best practice suggests that where a surrogate key is used, there should always be another way of uniquely identifying rows in a table. In your case, it might be via the addition of a timestamp column. – Strawberry Feb 13 '13 at 08:28
  • I think the primary key is where I was getting confused... I am going to use just two fields in my points column with id being the primary and corresponding to my user_id – DigitalMediaGuy Feb 13 '13 at 08:45

0 Answers0