Organization Id Field as a Composite Primary Key

Question

We currently have a system where each client gets their own database when they sign-up. Not surprisingly this is getting out of hand.

We are preparing merge all these databases together to a single db. In order to do this we need to note which organization the row belongs. How best is this handled on the database and what pros/cons (Speed, Maintainability, etc) are there to the methods below?

Option 1: Make the Organizatio nId to all tables and make it part of the primary key (making all keys composite).

Option 2: Add Organization Id to all tables as a column with a Foreign Key to the Organization Table.

Option 3: Something else.

We are looking at moving to NHibernate with this move, if that has an effect on what is done.

Answer 1

The company I work at uses option 2 and speed and maintainability have not been too much of an issue. As for speed as long as you set up your indexing correctly you queries should be pretty fast.. And for maintainability I would say that option 2 is definitely a step in the right direction compared to what you are dealing with now. It has been really easy to work with and all you have to do is a join in your query and you can add any client info you need.

Cdonner made a good point. In those tables that you are going to be querying alot it would be a good idea to have a composite key. They can help with performance of queries if you properly index your tables

Answer 2

I think I would go with #2, but it would depend on what your primary keys look like on your tables, and if you would have to worry about collisions if organization id wasn't in the key.

Just make sure you index it though, as you will likely be including it in lots of queries!

Answer 3

Option 1 and 2 are not mutually exclusive and you should do both. A foreign key constraint on the organization helps ensure that your organization keys are valid. A composite primary key is the only way to achieve uniqueness, unless you want to re-key all your records, and it is also valuable for lookup performance. Alternatively, you'd have to create a new primary key column on the combined data, an identity column, for instance. Such a change would likely require more changes in other places.

Answer 4

With this design, you will severily break all your queries for sure. You'll need to add one more field to each query, and you will forget to do this for the most important one.

So you really really need to use GUIDs if you still want to join your databases. This will simplify the transition greatly.

That's of course if you don't use your primary keys for ordering.

If you do, you'll need either to add this field you want, or, which is more preferable, add a fake ordering autoincrement field and still use GUID's as primary keys.