The Old Joel on Software Forum: Part 3 (of 5) - Implemention of the database structure

Implemention of the database structure

Hi,

Can somebody suggest a best approach to Implement the database structure.

We are in the process of developing different Web based applicaitons running at different locations. Application and database is planned to maintain at one location. The application are devleoped based on the locations business and thier process. I mean to say the business and process are different at each locations, of course there may be few data/information same at all the locations.

The applciation is built on iterative model. we foresee lots of changes to the data/table structure in future.

I foresee number of changes in the table structure in the future for applications running at one location.

My problem is should the organization go for multiple databases for each location or single database for all the locations.

I need to come up with some architecture so that
1. Application should not be difficult to modify in case of any change done to the table structure.

2. Avoid hard-coding in the application.

pls let me know, Is it good idea to few tables for one application and few tables for another application and so on.?

Pls suggest an approach that can ease us in future enhancement and modification to the applications.

Regards,
Chintu

Chintu S
Wednesday, January 28, 2004

This is homework?

Li-fan Chen
Thursday, January 29, 2004

Well, it seems there are a few options for this if I understand you correctly:

1. Create a schema and add a "site" identifier for each site instance. This ID would be used in virtually every table to identify one site's records. The disadvantage here is you must rigorously test that one site cannot modify another site's data. Depending on the application, this can have some major legal/IP issues to worry about with multiple sites.

2. Create a schema and create copies of the schema within a single DB, where each site has a different prefix for the objects. For example the customer table would be xyz_customer for the xyz site. This should be avoided as it leads to messy organization of object names that have a dynamic element to them.

3. Create a schema and load the schema into a separate DB for each instance of the site. This is nice because you use a DB for each site and the DB can be logically named for the site. All is kept organized within the DB. Keep an extra table or SP that returns the schema's version number so you can keep them all in some order. Invest in a sql diff program to look at any schema differences between the site instances. Additionally the DB's can be set up with permissions that limit the site to that single DB and not other sites. It is also easy enough to script the process of creating a new DB and associated logins for a new site. This is probably the best approach if not going for #1.

Enough rambling...

m
Thursday, January 29, 2004