Hi Thomas,
You are right, many schemas implies many tables.
What I like of the may schemas approach (one per user) is that I can
partition the data very neatly and resolve the data isolation simply with
the connection:
public Connection getConnection(String userId){
Connection conn = pool.getConnection();
* conn.createStatement().execute("set schema USER_"+userId);*
return conn;
}
The code above ensures that the user views only his tables (and data) by
default.
I'm not expert and you know much better the database internals, how it
scales and how to prevent future problems... I'll change my current model
(many schemas) to a single schema.
Regards,
Pablo
2013/9/21 Thomas Mueller <[email protected]>
> Hi
>
> You could do both, but I would use one schema. (the title of the mail is
> incorrect: many schemas is many tables, which is what I would avoid).
>
> Regards, Thomas
> Am 12.09.2013 17:54 schrieb "Pablo Beltran" <[email protected]>:
>
>> Hi,
>>
>> I need to store lot of data (even millions of records) on an small bunch
>> of tables for many users (thousands). What would be would be the best
>> approach?
>>
>> 1. Create one schema per user, so each user would have its own private
>> table: select * from USER_N.TABLE_X
>>
>> 2. Create one table shared by all the users and add a new column to
>> identify the data by user: select * from PUBLIC.TABLE_X where user=N;
>>
>> Thanks!
>> Pablo.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>>
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/jp_fVH9XqXU/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
