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.