How do I go about handling the shifting of news articles in regards to main, featured, and category areas?

Question

Consider a template such as this ( questions posted below ):

Let's say I posted an article about the Superbowl and tagged it in the Sports category. In my articles table I have a flag for featured or not, and a flag for main article. So if I check the main flag it shows up as the main article. If I post another main article a day later it will be gone from the main featured area #1 unless I delete the latest main article, it won't show up in area #2 or area #3.

Question #1: I could solve this by updating my SQL logic for area #2 by checking for both main and featured articles, and weed out the last main article, would this be the ideal way to handle the situation if I wanted it to shift down to the regular featured articles?

Question #2: But if I made my sql logic entirely based on the latest posted and a user wanted to specify an article as the main featured regardless of date, should I add extra logic in for this? So for example there could only be one main at a time throughout the entire articles table. Or should I force the user to update the article date so it's a requirement for the main to be the latest posted?

Question #3: And let's say that after my Superbowl article rotates from the main to become the first, second, third, fourth featured I wanted it to show up in my category listings as the first article under Sports - to do this I would need to unset the main and featured flags, would it make sense to add code in my article submission code to only have 4 featured and 1 main at a time? Obviously if one gets deleted then it can't shift backwards.

Would appreciate any advice on handling the organisation and display logic.

Answer 1

if you call these queries in order why don't you just do a

NOT IN ([ids that have already been used])

i.e. once you've chosen your main article you specify that the id shouldn't be in the featured select. then the featured ids and main id shouldn't be in the article list.

hope that makes sense.