Hi, If I am understanding your question correcly, I woudl go with option a, where you have one database and a few tables in the database, data in differentables are linked together via a common customer id. This seems reasonable and will be very fast with indexing.
Here are some problems I see in the other options. b) with a table per customer, it will be harder to run aggregation functions across all the talbes You may not see the need for this now, but it will likely be the case down the road. It will be harder to create queries which return multiple customers as it will involve multiple tables in the query. Also, I don't think you can paramaterize the table value within a perparedStatment. By not being able to do this, you will loose some of the security and performance of preparedStatemnts. Finally, if you want to change a datatype (or the like), you need to make sure that it is done on every table, not just the one big table. c) using a separate database will have all the bad characteristics of (b) - just even more pronounced. Also, you will loose the benefits of connection pooling, because a connection is to a single database, this is partially important because you mention that you will use server mode. Thus, you will have to constantly create and destroy connections each time you want to access a customer. In short, option a, seems like the most standard way to do it (and standard is often good). -Adam On Monday, June 13, 2016 at 8:21:25 AM UTC-4, claudiomc wrote: > > Hello, > > I will build a web application and use H2 as the database system (server > mode). > > The system will have 3 main tables, they will not be large in number of > columns, but they will have millions of rows some day. > > Each customer will have totally separate data inside each table, > identified by customer ID. > > Considering performance, my question is: what is the best approach for H2? > > a- 1 database with single tables (3 tables in this case, with a > column/index customerID in each table) > > b- 1 database with multiple tables (3 separate tables for each customer) > > c- 1 database per customer > > Thank you > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
