[GLLUG] Re: MySql: Database inside of a Database

jason D. Justman justmanj at msu.edu
Fri Feb 13 04:20:27 EST 2004


either way, multiple 'multiple myisam-database' database or a 'multiple 
myisam-table' database to represent an subset of attributes of a 
relational database is incorrect design.  while it is true that innodb 
collapses to a single tablespace, the option for seperation table spaces 
is new with 4.1.1. 

but regardless, you would no longer have a relational database.  if 
every 'sub-database' represents one primary entity, there is no relation 
that is implied by the structure of the design.  properly designed and 
enforced (foreign) keys will do the work that you are requesting.

e.g. something like:

create table user (
id int not null primary key auto_increment,
username varchar(32),
index username (username)
) type=innodb;

create table stats_db (
sequence_id int not null auto_increment,
user_id int not null,
value_1 int not null,
index user_id (user_id),
foreign key (user_id) references user (id) ON DELETE CASCADE,
primary key(sequence_id, user_id)
) type=innodb;

mysql> insert into user set username='abc';
Query OK, 1 row affected (0.00 sec)

mysql> insert into stats_db set user_id=1, value_1=10;
Query OK, 1 row affected (0.01 sec)

mysql> insert into stats_db set user_id=1, value_1=20;
Query OK, 1 row affected (0.05 sec)

mysql> insert into stats_db set user_id=1, value_1=30;
Query OK, 1 row affected (0.00 sec)

mysql> select * from stats_db;
+-------------+---------+---------+
| sequence_id | user_id | value_1 |
+-------------+---------+---------+
|           1 |       1 |      10 |
|           2 |       1 |      20 |
|           3 |       1 |      30 |
+-------------+---------+---------+
3 rows in set (0.00 sec)

mysql> delete from user where id=1;
Query OK, 1 row affected (0.03 sec)

mysql> select * from stats_db;
Empty set (0.09 sec)

now the database can accept responsability for the deletion of a user, 
and cascade it's subsequent related data.

likewise, direct access via "external" clients is generally not a good 
idea for security nor data integrity.  when one relies on the 
application to maintain proper dependency, the application layer must be 
responsable for the database model constraints.  if you are collecting 
specific stats, i would recommend a simple piece of application 
middleware (e.g. simple insertion script in php) that is responable for 
basic verification and error checking before accepting placement into 
the database. 

j

Scott Henry Harrison wrote:

>> Seth Bembeneck said:
>>
>>> Is it possible to have a database inside of a database?
>>> Or maybe some one can offer a better solution:
>>
>>
>> A better idea would be to design the database to represent the fact that
>> it will have multiple users.  Create a foreign key on each table in the
>> database that corresponds to the user.  This is much more practical and
>> maintainable than a separate database for each user.  Contact me off 
>> list
>> if you need some help designing it.
>
>
> Note: if you are dealing with a large number of users (e.g. 10000) on an
> ext2 or ext3 filesystem, a multiple-MyISAM-table database could have
> performance penalties.
> MyISAM is the default table type for MySQL tables.  I understand that
> another table type, InnoDB, can have multiple tables in one file
> though, and in that way would be better for ext2/ext3 for
> handling thousands of tables.
> Depends how popular "delta force" stats keeping is though. :)
>
> _______________________________________________
> linux-user mailing list
> linux-user at egr.msu.edu
> http://www.egr.msu.edu/mailman/listinfo/linux-user
>
>



More information about the linux-user mailing list