Database Schema for News System
database, database-design, mysql, php, schema
Solution
I initially stored the sub-articles in a separate table, but then I realised that the fields were essentially the same: Headline, Copy, Image. So why not just put them all in one big table?
Because referential integrities are not the same.
That is, of course, if you want to restrict the tree to exactly 2 levels. If you want more general data model (even if that means later restricting it at the application level), then go ahead and make a general tree.
This would probably look something like this:
Note how both PARENT_ARTICLE_ID and ORDER are NULL-able (so you can represent a root) and how both comprise the UNIQUE constraint denoted by `U1` in the diagram above (so no two articles can be ambiguously ordered under the same parent).
Problem
I have a news system I'm designing, and it seemed straight-forward at first, but as I've pushed forward with my planned schema I've hit problems... Clearly I haven't thought it through. Can anyone help? The system requires that the latest 20 news articles be grabbed from the database. It's blog-like in this way. Each article can have sub-articles (usually around 3) that can be accessed from the parent article. The sub-articles are only ever visible when the parent article is visible -- they're not used elsewhere. The client needs to be able to hide/display news articles (easy), but also change their order, if they desire (harder). I initially stored the sub-articles in a separate table, but then I realised that the fields were essentially the same: Headline, Copy, Image. So why not just put them all in one big table? Now I've hit other problems around the ordering. It's Friday evening and my head hurts! Can anyone offer advice? Thanks. Update: People have asked to see my "existing" schema: ``` articleID * headline copy imageURL visible pageOrder subArticleID * articleID headline copy imageURL visible pageNumber pageOrder ``` Will this work? How would I go about letting users change the order? It seemed the wrong way to do it, to me, so I threw this out.