Quick Question

[N-Bomb(Nerd)]

Is there a maximum amount of tables per database? I have an idea for a new application I want to create and I'm wanting to use php and mysql to store the values. However the only way possible I believe is to create 10 different tables for each user.

 

I have 10 different categories and they're always going to have additional stuff added to them, so it's not like I can have just one table per user.. right?

 

It's pretty much going to keep stats of their personal history. It's for a weight loss website, and for example I need to track their weight, so I figured for example that each user would have their own weight log table.. or is there a different method I should take here?

 

There's probably a better way to handle something like this and I'm just overlooking it.

[Ken2k7]

Is there a maximum amount of tables per database?

Yes, there is.

 

Having 10 tables per user is stupid DB design. I suggest learning DB design. You can have 1 table for members with a PRIMARY_KEY id field and then have another table for the additional stuff. Use member ID to reference the member.

 

You probably only need 2 tables from the information I have read in your post.

[N-Bomb(Nerd)]

But I'm going to have multiple values for each thing, for example on the weight loss log.. it logs the weight/date etc.. How can I have multiple values for something then with a single table? All of the tables are going to have new values added to it just like the weight log so I don't really see how this is possible.

[Ken2k7]

You do know a table can have multiple columns right?

 

Give me an example you have in mind and I'll tell you how one table would work.

[N-Bomb(Nerd)]

Okay, let's say the user "Jenny421" created an account and set her weight at 195 pounds ( that gets added to the database).

 

Then a week later she loses 10 pounds and updates her log and sets her weight at 185 pounds. I want the new weight loss added her to table, along with the current date.

 

However, I still want her original weight there in the database as well so I'm able to display her progress over time.

 

That's why I was figuring a table just for weight log for example.. that way it would be easier to keep track, plus that's the only way I know of doing it.

[mapleleaf]

CREATE TABLE `weight` (

  `id` int(11) NOT NULL auto_increment,

  `member_id` int(11) NOT NULL,

  `weight` varchar(10) NOT NULL,

  `date_added` int(11) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

 

 

Create that table.

Enter the weight for each user as often as you like. Check their weights against their member_id to do whatever calculations you might need.

Do you know codeigniter as I just did a site with this in it?

[Ken2k7]

Okay. The two tables.

 

Table 1

--------------------

users

id NOT NULL AUTO_INCREMENT PRIMARY KEY

name NOT NULL

date_registered NOT NULL DEFAULT CURRENT_TIMESTAMP

 

Table 2

--------------------

weight_log

id NOT NULL AUTO_INCREMENT PRIMARY KEY

userid  <-- the id field of users above.

weight int

log_time NOT NULL DEFAULT CURRENT_TIMESTAMP

 

Okay so let's say Jenny421 has id 10 in the users table and she registered now. And she registered with 140 lbs. Then in the weight_log table, the userid is 10 (same as her id in the users table). This helps you distinguish who is who. In the weight field, put 140 and the log_time would be whatever it is now. If you have the default there, you don't have to insert into that field, MySQL will do it for you. That's the time when she submitted that information.

 

Then say 3 days later she updated it. You add another row to weight_log with id 10, and her new weight. Again, log_time will take care of itself.

 

If this doesn't make sense, read up on DB design.