Splitting a table - a good idea?

• pauldurdin

  SSC Eights!

  Points: 835

  More actions

  March 28, 2003 at 12:44 am

  #452426

  I have a couple of places where splitting a table is likely to be useful - in both cases there are two distict areas of information that are updated by separate people in separate processes, however i have never been quite sure as to the best way of managing two tables;

  1. you have to create and destroy the secondary rows when you insert into primary table.

  2. I guess that i'd end up with doing joins a lot of times to return info to the users, and i guess that starts to defeat some benifits of second tables.

  3. How best to 'join' the tables? Have foreign key in the secondary table(s) or foreign key(s) in the primary table?

  (There always seems to be a 'primary' set of core info that users use to indentify the row).

  quote:

  ---

  Splitting a table can have advantages.

  If it is a table that has several fields that are frequently read

  but never updated and other columns are read and updated.

  I ran into situations where only a few fields were updated and

  a lock on the entire record would have impact on other users.

  The table was split and only the sprocs were modified, the

  locking problems were solved.

  I know this is only an example but things like this can happen.

  ---