collapsable tables

[greenie__]

Hi

a co worker who outranks me is insisting on have a user table with a field called userdb. which is a mysql dump that is inserted into their record. when they login it extracts this dump into its own database. building tables and records that are in this dump.

 

Then when logging out, a msql dump will be made,then update userdb field and the database deleted. It has caused some problems with simply updateing tables requireing scripts to change tables. And casual queries for statistics and such are annoying requiring extraction if necessary and collapsing if it was extracted otherwise people suddenly lost database.

It is frustrating me. The only way he would give this up if there is some danger that can not be worked around. Does anyone know what issues this could cause?

[fenway]

Huh?

[greenie__]

Hi sorry it doesn't make sense.

 

There is a table in a database that will always exists. Which contains a table users. Inside table users is a field,userDB, which has a mysql dump of a users database.

 

staticDB

  Users

      userID int primary

      username

      Password

      userDB mediumblob

 

 

Every user has there owns a mysql dump, with the exact same table structure in each users own database. The mysql dump will look something like.

 

-- MySQL dump 10.11

--

-- Host: localhost    Database: myuser40

-- Table structure for table `myTable`

--

 

DROP TABLE IF EXISTS `myTable`;

CREATE TABLE `myTable` (

  `myKey` int(11) NOT NULL auto_increment,

  `myComment` varchar(255) default NULL,

  PRIMARY KEY  (`myKey`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

 

On user log in he takes this userDB puts it in text file i think then imports it the database (which he creates for the user) then the user connects to that database and uses it. Then when the user logs out it makes a mysql dump, then does mysql insert statement with the txt file into the database, and then drops the database. This is a web app and done in php by the way.

[greenie__]

O and server version for mysql i think is 5.0.32-Debian_7etch6

[fenway]

On user log in he takes this userDB puts it in text file i think then imports it the database (which he creates for the user) then the user connects to that database and uses it. Then when the user logs out it makes a mysql dump, then does mysql insert statement with the txt file into the database, and then drops the database. This is a web app and done in php by the way.

WHY?

[mrmitch]

So, rather than have one database and keying/linking all the rows with the unique user id, he creates and destroys similar databases for each user every time they log in/out.

 

Interesting idea, but I'm with fenway - why??

You would have to be seriously cramped for database space in order to think this was a good idea. Query wise, MySQL is fast enough to work on extremely large databases, so there's no reason to worry about table size, in most cases.

 

Security? There are better ways.

 

[Barand]

Interesting idea,

 

 

I suppose that's one way of describing it. IMHO, here's a few more

 

bizarre, burlesque, comical, crazy, farcical, foolish, grotesque, laughable, ludicrous, nonsensical, outlandish, preposterous, risible, silly, zany 

 

 

[greenie__]

His reply to "why?" was he doesn't want lots of databases everywhere. So he wants them to collapse down.

 

then to "why have lots of databases?" his response was

Now some users may have many users under them. Kind of u register a company and u can have single or many. He thinks it is best to have separate database for each company.

 

also after he came up with this, a customer request was that they hold the data on there own mysql server but still use it through our site. Which unfortunately having separate databases will kinda make it easier.Though still an odd request. Cause they ask us for a page, we go to them and ask them for data then respond back to them ><.

[greenie__]

O and i can not imagine this being faster, because on log in your running all these queries just to build a database and have the information that they have inputted. After they have logged in it may be slightly faster, with less information to process.

 

Also IF they log out and don't just close window he executes a mysql dump turning into text, which would take more power, then insert a large text file into that users record which would be another big hit.

 

I think its a loss cause and will never change. Only way to end frustration may be to quit my job.

 

Also no hosts will allow the php exec command(security obviously) unless they use a dedicated server. Which has made it 5 times more expensive.

 

I have hopes that some how this will cause a problem that can not be worked around in mysql.

[mrmitch]

 

 

I suppose that's one way of describing it. IMHO, here's a few more

 

bizarre, burlesque, comical, crazy, farcical, foolish, grotesque, laughable, ludicrous, nonsensical, outlandish, preposterous, risible, silly, zany

 

HAHA - You have a knack for explaining things.

 

 

As for greenie's problem - I still see no reason you'd want to do all that work when you can just have each record linked to the user responsible by their id or whatever you use to recognize them. This is a relational DB for a reason - to build relationships.

You're going to have to make update scripts that run whenever these people log in, which takes more of their time, whenever you need to perform maintenance, or have a script that logs in as each user to update their db, taking time and resources. How many db's are we talking about anyway? Maintenance alone would be a bear.

 

 

 

[greenie__]

I suppose that's one way of describing it. IMHO, here's a few more

 

bizarre, burlesque, comical, crazy, farcical, foolish, grotesque, laughable, ludicrous, nonsensical, outlandish, preposterous, risible, silly, zany 

 

 

 

I agree and I think you were being generous.(clearly i am biased though)

 

as for mrmitch simply maintenance is painful, and we only have 5 test accounts at moment. It could be little as 40 users to over 100. Or even more, since there is talk of 1 month trial accounts being made.

 

Though I have picked up since this guy is friends with the boss, that he has started telling tales and generally being manipulative. Since I questioned his grand scheme and pointing out how he broke pages. It is not like I was being mean about it, he just doesn't do php normally. It is a lost cause, and thank you for the tips.

 

Last tips from what I have learned from this job.

Learn about project management especially "Change for request form". Make sure your client will never want to use them unless absolutely necessary. If they refuse to conform with this stuff then your going to have headaches><.