Just a quick question

[PHPEnthusiast]

Is it ok to create separate tables for user stuff? Example, I am using the user's ID to create their own unqiue table so that they can use them instead of having them all in 1 table.

 

So instead of having a table like

CREATE TABLE IF NOT EXISTS `user_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I have something like

CREATE TABLE IF NOT EXISTS `1_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `textfield` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `2_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `textfield` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `3_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `textfield` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So that each user can track their own logs. Is that an ok thing or is that going to waste a lot of resource and be redundant?

[requinix]

Very wasteful. Your application should never have to routinely modify your database schema.

 

One log table and include the user ID in it. Remember that database servers are built for this exact kind of work. Don't try to outsmart them.

[PHPEnthusiast]

Very wasteful. Your application should never have to routinely modify your database schema.

 

One log table and include the user ID in it. Remember that database servers are built for this exact kind of work. Don't try to outsmart them.

But wouldn't it just be easier to store specific tables in a specific database? There are actually more things to this then just logs. The log's ID gets grabbed from the first database which is the main database. Then the log summary gets grabbed from the second database in which it is stored by the user's ID. Is that still a waste? I keep thinking that if I actually stop doing all this and store everything in 1 database, it will get filled up and it would be hard to manage. Basically, everyone's log would be in 1 table and trying to find if user ID (13) has an existing log in a huge table would take longer then expected while if it's stored in a separate database with the table name "13_logs", it would be easier to find and manage because if the log is empty, the icon would be greyed out. If it has entries, the icon would be blue.

[requinix]

It won't be. Remember what I said about not trying to outsmart the database? Really. Don't do it.

 

All you have to do is put an index on the user ID in the table. That tells the database where to look for records for user #13. Then everything will work smoothly.

[maxxd]

Actually, the scenario you're concerned about is exactly what SQL is designed to do, and it does it very quickly. Because you're indexing everything by user ID, that's the only column that needs to be scanned. Of course, speed of transactions can slow down, but that's usually due to bad programming - for instance, selecting every column in a row when only 1 is needed.

 

Read up on database normalization - a good article or three should shed some light on the situation and why working as you've proposed is actually far more wasteful than working the way you're concerned about working...