规划新闻订购系统

Question

Having in mind that there are thousands of news in a table called sn_news, where some news may be available for X clients at the same time but with different id, I was wondering on how to implement an news ordering feature!

Showing X news for Client Y in its panel, like so:

ID          Category            Title
_______     __________________  ____________________________________________
1572709     Futbol Earthquakes  Lluvia de goles en San José
1568657     Eventos Locales     Maná rayó el sol de la Bahía
1562970     Deportes Locales    SJ Earthquakes vence a RSL, es líder
1546516     Locales             La magia de la música en la niñez
1546513     Locales             80 Años de Fotografía mexicana en SFMOMA
1546511     Locales             Clases gratis de diseño solar en West Valley College
1546507     Locales             Javier Sicilia visitó   12 de abril de 2012     Locales (noticias cliente)
1546504     Locales             Celebrando el español a través de la poesía
1546502     Locales             The National Hispanic University Receives Commendation from San Jose City Council
1546498     Locales             Clases de costura en el CET
1546497     Locales             Centro del Este de San José al servicio de la comunidad
1546481     Educacion           Ayuda en español para elegir universidad
1546479     Educacion           Aumentan programas de idiomas en escuelas públicas de California
1546478     Educacion           Program uses visual arts as a better way to learn English
1546477     Educacion           Niño hispano de 4 años es pintor
1546473     Educacion           Perspectivas en el Sistema Educativo 2012

Which is the best way of ordering the news?

• Changing the new's id X with Y?
• Adding a new filed named news_order, where if the client has 20 news it will contain numbers from 1 to 20 according ordered by the id field, and therefor the ordering will be always done upon that field?
• Any other way?

Hope I explained my self, if not, please let me know!!

UPDATE

So just to make clear things, it's not my app, I did not build it and is a really f*** up code I just need to "update" it and add some new features. The app is relatively simple, but because the code and some of the database structure was implemented long long time ago and never got "improved", now are consequences.

There is a date field, and that's how they are ordered when the news are retrieved (sorry for the mistake).

The repeated news with different id is because of how the app works, it's complicated but logical.

Answer 1

You should not use the ID field for anything else than a unique identifier. Using it for any sort of functionality, including ordering, is bad practice.

Adding a "sort_order" field is the proper solution.

Still I have to wonder if you don't also have a field for the publishing date. Seems like it should be in there somewhere if you're handling news. If you have a publishing date, then the most appropriate thing to do is to order by that (with "DESC", presumably).

But also I must wonder, like 'nadirs' in a comment, why you have duplicate entries for different clients. The appropriate solution is to have a table for clients, a table for news and then an intermediary table to bind them together. But that's outside of the scope of your question so I'll leave it at that.