CELKO (9/20/2012)
This is not how we do this in RDBMS. Your second table is a subset of the first. We would use a VIEW to get a subset. If there is any difference in the subset that is important, then it is modeled with scalar values in a column. That is called "The Information Principle"; it is one of Dr. Codd's 12 rules.Rows are not records; we do not have "links' in RDBMS; that is a term from network databases. your mindset is still locked back in punch cards and want to keep two tables to mimic decks of cards. We treat a virtual table (VIEW, CTE, derived tables, etc) as a table. We hate triggers and procedural code, which is more of that punch card mindset.
You can partition a table if some of the data needs to be accessed more than the rest. All you are getting are kludges to maintain a fundamentally bad schema design.
I'm just guessing here but he probably wants an historical table, to store data changes made on a row.
He could, obviously, use the same table and add a column "ActualRecord" and have a partition on that column where the value is 1 and 0 and always use the 1 for current records (build a view over it)...
But sometimes is best to have a table to store the data changes since it's only use for audit porpoises...
And in some SW solutions for small business companies partitioning can decrease performance since they only have SATA disks with no RAID or whatever....