Database Design Question for a Newbie

[djminus1]

I am attempting to build a RIA using Flex3, PHP, and MySQL. I am really a newbie when it comes to MySQL. I need some help in desiging my database for this application.

My application will allow people create a user account, and to log information they would like to keep track of. For this example, lets say they are logging bicycle rides. Each user will have one account, but will log many bike rides.

I would obviously have one master user table containing all user information, with a userid primary key. Would I need to create a seperate table for each user to log their bike rides? If this is what I should do, how would I create these tables automatically when a user registers? Here are basic examples of the tables I think I need...

USERS
userid INT PK
username VARCHAR
user_first_name VARCHAR
etc...

USER_BIKE_RIDES
userid INT
rideid INT PK
ride_date DATE
ride_distance INT
bike_used VARCHAR
etc...


Thanks, this will really help me!

Kevin

[Atli]

Would I need to create a seperate table for each user to log their bike rides?

No, that is usually not a good idea.

The typical way to create such a database would be to create one user table and one log table, like your example showed.

Then the two tables would be "connected" via a Foreign Key column in the log table, which would reference the userid in the user table.
This is typically called a one-to-many relation (1:N), because a single row in the user table could be linked to multiple rows in the log table.

The syntax to create such a relation could look something like:

CREATE TABLE `User` (
  `UserID` INT Unsigned Not Null Auto_Increment,
  `UserName` VarChar(255) Not Null,
  /* Etc... */
  Primary Key (`UserID`)
)Engine=InnoDB;
 
CREATE TABLE `Log` (
  `LogID` Int Unsigned Not Null Auto_Increment,
  `UserID_FK` Int Unsigned Not Null,
  `LogValue` VarChar(255) Not Null,
  /* Etc... */
  Primary Key (`LogID`),
  Foreign Key (`UserID_FK`) REFERENCES `User`(`UserID`)
)Engine=InnoDB;
 

Note that I specify the InnoDB engine when I create the table in order to make sure that the Foreign Key constraint is enforced.
The MyISAM engine simply ignores them.

[djminus1]

OK...one more dumb question. Since we are assigning a foreign key to the "Logs" table to enforce the relationships, if I delete a user from the "Users" table, will that automatically remove the logs for that user from the "Logs" table?

[Atli]

That depends.

By default, if a DELETE or UPDATE statement tries to alter data that is referenced elsewhere, it will fail with an error.

But, you can specify what action should be taken in such an event by using the ON DELETE [action] ON UPDATE [action] syntax.

Like, for example. This would instruct the tables I showed earlier to remove all logs for a user when the user is deleted from the User table, and to update all logs if a user is updated.

Foreign Key (`UserID_FK`) REFERENCES `User`(`UserID`)
ON DELETE CASCADE ON UPDATE CASCADE
 

You can also use SET NULL rather than CASCADE in order to keep the records, setting the foreign key column to null.
Note that you would have to set the column to allow nulls for this to work.

[djminus1]

Great...thanks so much.