I have encountered a situation where the news and the news archive are in different tables.
Given that those tables contain many records and both share exactly the same structure is it still smart to split them up instead of having a flag that marks them as archived?
views:
41answers:
2
+2
A:
Your news table will be faster without the archive. You have to ask yourself if both are often hit in the same query. If they aren't then it may make sense to keep them separate.
Also have to ask yourself what archive actually means? 1 year old, 2 years old. I'm guessing it's just a flag to either show on the News Page or News Archive page. If you do merge into one table perhaps you could just use logic > 1 year old is archive.
You could also create a View that unions both tables together.
Ryu
2010-02-24 15:55:39
@Ryu: by UNION, you mean UNION ALL.
Adam Musch
2010-02-24 19:31:10
They use the same queries but as of now they are separated into different tables and different stored procedures. I am not going to change it since it's a legacy system that I didn't program myself but I am asking this so I'll know how to design for further projects.If I join those tables would it be faster to `SELECT` either the archived news or the news? Would it help if I make the dateCreated field as one of the primary keys?
the_drow
2010-02-24 23:11:08
@the_drow dateCreated would be good in a clustered index, but not a primary key.
Ryu
2010-02-24 23:17:53
And why is that?
the_drow
2010-02-25 11:38:57
+1
A:
You could also consider partitioning the data on whether the article is marked for archive or not. That way at least you could change the structure without remembering to fix the other table and you still get the faster performance out of the partition that contains only the active records. However, if the system is in place and working, it might be best just to live with it. I wouldn't pay you to make this change if I were your customer unless there was a problem happening with the current structure.
HLGEM
2010-02-24 16:05:07
I'm not asking this to change a legacy system. This is an existing situation which I am asking if it should be avoided.
the_drow
2010-02-24 23:12:10