x

SQL-Server DB design time scenario (distributed or centralized)

We've an SQL Server DB design time scenario .. we've to store data about different Organizations in our database (i.e. like Customer, Vendor, Distributor, ...). All the diff organizations share the same type of information (almost) .. like Address details, etc... And they will be referred in other tables (i.e. linked via OrgId and we have to lookup OrgName at many diff places)

I see two options:

  1. We create a table for each organization like OrgCustomer, OrgDistributor, OrgVendor, etc... all the tables will have similar structure and some tables will have extra special fields like the customer has a field HomeAddress (which the other Org tables don't have) .. and vice-versa.

  2. We create a common OrgMaster table and store ALL the diff Orgs at a single place. The table will have a OrgType field to distinguish among the diff types of Orgs. And the special fields will be appended to the OrgMaster table (only relevant Org records will have values in such fields, in other cases it'll be NULL)

Some Pros & Cons of #1:

PROS:

  • It helps distribute the load while accessing diff type of Org data so I believe this improves performance.
  • Provides a full scope for accustomizing any particular Org table without effecting the other existing Org types.
  • Not sure if diff indexes on diff/distributed tables work better then a single big table.

CONS:

  • Replication of design. If I have to increase the size of the ZipCode field - I've to do it in ALL the tables.
  • Replication in manipulation implementation (i.e. we've used stored procedures for CRUD operations so the replication goes n-fold .. 3-4 Inert SP, 2-3 SELECT SPs, etc...)
  • Everything grows n-fold right from DB constraints\indexing to SP to the Business objects in the application code.
  • Change(common) in one place has to be made at all the other places as well.

Some Pros & Cons of #2:

PROS:

  • N-fold becomes 1-fold :-)
  • Maintenance gets easy because we can try and implement single entry points for all the operations (i.e. a single SP to handle CRUD operations, etc..)
  • We've to worry about maintaining a single table. Indexing and other optimizations are limited to a single table.

CONS:

  • Does it create a bottleneck? Can it be managed by implementing Views and other optimized data access strategy?
  • The other side of centralized implementation is that a single change has to be tested and verified at ALL the places. It isn't abstract.
  • The design might seem a little less 'organized\structured' esp. due to those few Orgs for which we need to add 'special' fields (which are irrelevant to the other tables)

I also got in mind an Option#3 - keep the Org tables separate but create a common OrgAddress table to store the common fields. But this gets me in the middle of #1 & #2 and it is creating even more confusion!

To be honest, I'm an experienced programmer but not an equally experienced DBA because that's not my main-stream job so please help me derive the correct tradeoff between parameters like the design-complexity and performance.

Thanks in advance. Feel free to ask for any technical queries & suggestions are welcome.

Hemant

more ▼

asked Oct 27, 2009 at 11:14 AM in Default

Hmnt gravatar image

Hmnt
43 4 5 5

(comments are locked)
10|1200 characters needed characters left

0 answers: sort oldest
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108

asked: Oct 27, 2009 at 11:14 AM

Seen: 622 times

Last Updated: Nov 11, 2009 at 06:08 AM