Database Design – SQL Schema Change: Modify Existing Table vs Create New

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.

I am assuming that:

• you will have separate application for each industry,
• they have not very much in common (they really cover different business aspects)

I think that one of acceptable solutions in this case would be:

• a separate database for each industry
• in every database, single shared public schema for shared data, including common dictionaries, all non-customer-dependent data.
• one separate database user account + private schema for each customer. You might use postgres feature of table inheritance to conform to some public table.

Issues that Simon puts in his comment are really important. So you will have to enforce a very strict policy regarding GRANTs in your database.

In PostgreSQL, it is possible to achieve (as well in many other RDBMS). The key to achieving multi-tenant solution would be intelligent usage of schemas, roles, search_path setting. See http://www.postgresql.org/docs/current/static/ddl-schemas.html. Actually what I propose will in a way emulate what Oracle does. If you need help on details please ask.