SQL Server Performance

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

Discussion in 'General DBA Questions' started by Hmnt, Oct 27, 2009.

  1. Hmnt New Member

    We've an SQL-Server DB design time scenario .. we've to store data about diff 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 relevent 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 accesing 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 constraintsindexing 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 implemenation 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 'organizedstructured' esp. due to those few Orgs for which we need to add 'special' fields (which are irrelevent 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 thats not my main-stream job so please help me derive the corredt tradeoff between parameters like the design-complexity and performance.Thanks in advance. Feel free to ask for any technical queries & suggestions are welcome.Hemant
  2. melvinlusk Member

    A couple of questions you need to ask yourself:
    1) Will the number of organizations increase or stay static? As you add more organizations, maintaining a table for each will become more cumbersome.
    2) How many rows do you expect to have in these tables? If it's only a few hundred-thousand, you shouldn't have a hard time managing this data in a single table (provided hardware is adequate).
    Have you considered keeping each organization in it's own database?
  3. Hmnt New Member

    Let me refine before it goes farther ...The Org data will be updated .. lets say weekly or so .. some Org data might be updated frequently but its not a daily task. The main think is to be able to organize the data in such a way that it is easy to access and lookup the data based on its pointer (read PK) references - it'll be referred @ multiple places multiple times so the design must be sustainable for this kind of situation.Having a separate DB for each Org or even thinking about splitting the existing DB doesn't seem worth based on the past experiences. I hope this answers your concerns.
  4. Hmnt New Member

    Any update on my last comment ?
  5. melvinlusk Member

    I think your' best bet is to keep the organization data in a single table with an appropriate OrgID to identify the records. This seems easier to maintain than having a seperate table for each organization. Make sure that the OrgID is indexed properly. You may also want to consider creating a seperate table partition for each OrgID for performance.

Share This Page