Normalize out to different databases?

Question

What are everyone's opinions on normalizing a database out across multiple databases. For instance, say you had a database that allowed different clients to sell products. You had abstracted the concept of an order, order item, etc., but each client wants a different field added here or there.

You could add the fields to the specific table, creating a wide table that had values in it that not all clients utilized, or you could create a tall table that listed a column-name and value so as to make it appear that you are appending on columns to a specific object (for instance, wanting a Home phone number on the order table when there only exists a Work phone number).

Or you could normalize out to a client specific database that has a table called CustomOrder that keeps the original order id from the generic database and then adds the columns necessary to accomodate the client.

Has anybody used a solution like this? I know referential integrity would be hard to control across databases, but it does keep the original generic database small and lean while allowing clients to customize to their hearts contents.

What are everyone's thoughts on this type of design?

Answer 1

I would definetly recommend staying away from creating multiple databases it sounds like what you want is something like this Entity Attribute Value model. If you ever needed to scale out your approach of creating multiple databases would make that more difficult.

Answer 2

I once worked with the Siebel CRM system, which permitted heavy customization of their canned application. It did this by permitting extension tables. An extension table for, say, Customer, would have a one to one relationship with the Customer table, and would have all of the added columns in it.

Answer 3

It is a great idea to have something like that. In theory it works great. But even with a special handling of application specific fields you will get into troubles. Each application might have different business logic (validation algorithms). Now a record that is valid in one application might not be valid in another application. My recommendation would be this.

Keep the databases separate but introduce something that will link them together. For example for reporting purposes you might want to know that customer A from one application is the same as customer B from another application. Create a table (database) that links records from your various databases.

I wouldn't probably go further than that unless I know your concrete details of your particular problem.

PS: What you are talking about is known as Master Data Management. There are commercial products that tries to solve this problem but none of them convinced me yet that it really works (yes, it was for querying data but how about updates?).

Answer 4

I've worked in that environment and it became a real nightmare. We had a main application which was managed by one team and others managed the client specific customizations. THe problem then became that multiple clients got customization that were simliar but not exactly the same (although they could have been if anyone every looked at what other clients are doing before they design their own). Since the team that did the main application that applied to everybody was paid for by the company and not the individual clients, we never had enough people there to generalize specific client needs so that other could use them when the need arose and so created 20 different custom solutions to do the same thing. Further, as client deadlines tended to be sooner than the general team could handle (there were only two of them out of 100 developers), even when something would make a good generization, it was handled in the client database to meet the dealines. Soon there was such a big mess that refactoring the database to fix design flaws became impossible because there were so many different possible applications that could be affected, no one dared to try to refactor. Then what would happen would be a fix was put into place for a specific problem but the old method would stay there and each client would choose a differnt way to implement even the things in the main application database. Organizational changes to try to add a design team to this mess after it was created then caused an almost comlete halt the work that could be accomplished because none of the new work met the new design standards without a complete rewrite of the existing site (that of course the clients didn't want to pay for as the current version worked just fine to them). Stay very far away from this model!

Answer 5

I'm not sure how you associate the term "normalize" with what you suggest. But I would consider it a gross antipattern, unrecommended in any SQL theory or practice literature I'm familiar with.