Thank you Adam!
On Tuesday, June 14, 2016 at 3:19:51 AM UTC-3, Adam McMahon wrote: > > 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.
