August 4, 2005 at 3:08 pm
My team is trying to decide between two different approaches for designing a database for our application.
Scenario 1 - We build a single database containing the objects required by the application as well as any special objects required by specific clients.
Scenario 2 - We build one database containing objects required by the application, and a separate database for client-specific objects.
What do you see as the relative strengths and weaknesses of each approach?
“If you're not outraged at the media, you haven't been paying attention.”
August 5, 2005 at 6:10 am
here's the link to another post (kinda) similar in concept...
**ASCII stupid question, get a stupid ANSI !!!**
August 8, 2005 at 10:02 am
Let me be a little more specific about the issues we're facing:
The application will be a reporting and analysis application that takes in data from the client's OLTP systems. When you look at a given client's OLTP system, you find that they have essentially the same metadata for the same entity. For example, each client will have a "Customer" table that contains the standard name, address and phone number. However, a given client may add an extra column to their Customer table, such as "Region", whereas other clients do not. So, the question is how should we plan for the inevitable extra column(s) of metadata for an equivalent entity while developing our common schema for all of our clients?
The two positions that have developed within our team are (1) keep the common schema together with the client-specific schema versus (2) build a common schema and place that in one database and then build custom databases for each client to accommodate client-specific metadata. The common schema would consist of the database objects required to run the application while the client-specific schema would be for the database objects that contain the actual data for the client.
The basic, and so far only, argument in favor of keeping the two schemas together is better performance of queries.
I see several advantages to separating the two schemas. The first advantage is ease of maintenance: as we build new versions of the application, we only need to work with the application objects. Updates to the application can be applied with minimal modification to the client’s schema. Another is easier troubleshooting, since separated schemas will make it easier to find application issues caused by a particular client's implementation. Another is we can keep the database and the individual rows within a table at a more manageable size.
I am leaning towards separating the schemas into two databases, but I want to be fair to the other side. My purpose in posting here is to get feedback from others so that I can see points I may have missed.
“If you're not outraged at the media, you haven't been paying attention.”
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply