Re: multiple database dilemma

On 19 Aug 2002 17:34:11 -0700, tim erickson <dockersblack_at_hotmail.com> wrote:
> Hi,
> I'm designing the database of an E-commerce project.
> Think of one branch of an organization. I made the design
> and it's like 15 tables. Estimate number of users would be
> around 500.
> (assume only %20 of them access to the system daily
> and the largest table will have max of 100.000 records and
> number of branches are very likely to increase up to 300-400.)
>
> Now the problem is; think of 50 of these branches. Exactly the
> same database structure would be required. My dilemma is;
> 1. I can add a branchID field inside every table
> 2. I can create a seperate DB for each branch.
>
> Let me not forget to mention that the queries are not
> multidatabase queries.
>
> The disadvantage of the first approach is, tables will be overloaded.
> One branch would be using only its portion of the table (1/50) where
> total table size will be 50 times larger. This will slow down the
> joins and selects. The performance is crucial since its a web application.
>
> My concern with the second approach is, same pages would be
> serving different clients simultaneously, there will be simultanous
> requests from the WEB server which switches between different
> databases. And this might also slow down the performance.
>
> Am I thinking reasonable or is something missing.
> Please advice.
> Thanks in advance.

You are thinking reasonably. Based on your description, the system will scale by adding branches. You seem to be comfortable with creating separate databases for a branch - another way to look at this is that you are partitioning the database by branch. The main concern is whether this partition is appropriate within the business rules of the system; in other words, will different branches share data in any significant way? If not, partitioning by branch is very natural and reasonable.

I think your concern about switching databases is probably not a serious performance bottleneck, although I wouldn't rule this out. Any performance issues will probably be platform specific and dependent on data file configuration, etc. Your connection pool can probably also mitigate this problem; this is another area where the profile of the solution will be platform specific. Received on Tue Aug 20 2002 - 07:05:12 CEST