design question -- separated database?

[elaine3839]

Hi,

I am working on a project whose database has 3 types of tables --- static tables(simply for lookup purpose, doesn't change after being created), regular tables for application and logging tables. Logging tables are mainly for reporting purpose and tend to be very large.

This application is service provider kind of app which means it saves multiple company's information. Besides the static tables which are the same for all companies, different company's info are mixed into the regular application tables and logging tables. i.e. for a certain regular table, it will have records for different companies.

This poses at least 2 problems from the perspective of scalability:

1. Since all logging information are saved on the same db, that makes reports slow if there are millions of rows, even if a single customer only has a few thousand record in that table.

2. Have all customers' data in the same db server makes it hard to move customers to different server if their needs exceed the capacity of the server. Using query to export might be a choice, but it could be very painful when there are lots of RIs between different tables, and error prone.

Since this application supports both SQL Server and Oracle, for SQL Server, they are thinking about put different company's data into different databases(including regular tables and logging tables) while put the static tables into a common database so every other database can access it.

Since Oracle using table owner instead of database, shall I just create different company's table under different user name to resolve the above issue? Or should explore the possibilities of put different data/log files to different physical drives to improve performance? Any other good ways to get it resolved?

Thanks in advance for your reply!

[marist89]

1. Not a big deal. This is where partitioning and indexes come into play.

2. Bigger problem. I would seperate the data in schemas, one for common tables, and one for each "company". That way, you would have an easier time moving a company between servers.

An even more scalable system would be having an instance for each "company" and an instance for the common tables. Since the common tables are relatively static you could either use replication to copy the data to the other instances, or you could use distributed databases to "link" the "company" instances to the "static" data. While you are small, the instances could reside on one box. When they grow, they can move to different servers transparently.

[svk]

You probably will have to consider quite a few other factors before you really come to a conclusion. But here are a couple of things :

Putting every customer's data in same schema should be avoided. But that is not because your reporting will become slow. All your regular and log tables will certainly have a column called "CUSTOMER_ID" or something that identifies a customer's data. This will also surely be part of some index. I'll expect that even if millions of rows get generated in these tables, percentage of rows pertaining to a single customer will be very small. This will not make your reports run tooooo long as you are probably expecting. All those reports when they will access the tables using the CUSTOMER_ID based index, it will not be too slow compared to if you have different schemas for each customer.

For maintenance reasons, it will be good to have a dedicated schema for every customer. That will allow you not only placing the tables in different tablespace but also making them different in size because not every customer will have same volume of data. If you are using 8i or better, transportable tablespaces will make the activity of moving one customer from one database to other very simple. There will a long list of advantages if you make different schemas for every customers.

And obviously, there are going to be downsides to it as well. From the database owner's point of view, will there be any need of running reports for "all" the customers ? If yes, making different schemas will make your life miserable. When it will come to say "adding a column to a refular/logging table", you will have to do it across all the schemas.

An yes, you are right, "different schemas" in ORACLE can be very synonymous to "different databases" in SQL Server.

How is going to be the connectivity ? How is the application going to access the database ? Even this may have an effect on the strategy you follow.

If there are any compelling reasons to make a single schema, see if range based partitioning can help you.

[elaine3839]

Thanks a lot both! I really appreciate all your feedback!

Now I am leaning toward making it multiple schemas since I feel it will be easier to implement than multiple instances and also has lower server hardware requirements(correct me if I am wrong).

So my question is:
How hard it's going to be to transfer from multiple schemas to multiple instances in case there are future needs?

To answer svk's questions/comments:
1. I also have the performance concern to query against different schemas, and even more for different instances(unless as Jeff said, copy the static part to all the different 'company' instances). Will the frequent use of static tables(which located in another schema than regular tables) make the performance unbearable or it should be OK?

2. We will only support Oracle 8i and later in this release, so transportable tablespaces will be very handy.

3. So far, I didn't see a requirement to run a report against all schemas while I need to double check. In case that's the requirement, is it more like 'miserable' than 'impossible'?

4. The application is written in C++, and they will use OLE DB to access Oracle database. I have little experience on that, so don't really know how is that going impact the db design. BTW -- knowledge on how to handle Oracle LOB using OLE DB is badly needed, so any info/resource on that will also be greatly appreciated!

Jeff -- I don't have much experience on distributed DBs. So if I go with the multiple instances approach, how should I link the common instance to other instances? Should I worry about performance of 'link'? I tried to avoid copy the static part to each 'company' instance since 1) it's going to take a lot of spaces 2) user will backup this part even if it's not changing at all. Does that make sense to you?

Also, for an Oracle server on Win 2K box (sorry, they are primarily MS shop here), usually what's the maximum # of instances we can create on a single box?

Thanks again!!!

[marist89]

Originally posted by elaine3839
Jeff -- I don't have much experience on distributed DBs. So if I go with the multiple instances approach, how should I link the common instance to other instances?

You would use a database link in the "company" instances. For simplicity sake, you would create a public synonym that pointed XYZ to XYZ@COMMON_DB.

Should I worry about performance of 'link'?

Depends on where most of your data will be pulled from. If the majority of your data is queried from the "company" database, performance of the link is minimized.

I tried to avoid copy the static part to each 'company' instance since 1) it's going to take a lot of spaces 2) user will backup this part even if it's not changing at all. Does that make sense to you?

Sounds fine to me.

Also, for an Oracle server on Win 2K box (sorry, they are primarily MS shop here), usually what's the maximum # of instances we can create on a single box?

Depends on resources. Usually the limiting factor is memory. More memory = good for oracle.