As part of the PHP/MySQL Content Management System which I'm constructing I have an article table which houses all of my articles and all of its components such as images etc. In addition to this I have a pages table which contains the content for all articles which have more than one page. I have a feeling that my table design isn't as good as it could be. Could anyone suggest improvements to what I have below please?
For instance at the moment I have the first page of the article in article table because most articles only have one page. The rest of the article's pages are in the pages table, is this a mistake?
I think I understand what you mean. But if I had, for example, all the 4 pages of the article in the article table I may have a problem with the article_id field because at the moment it is the primary key of the article table.
Doesn't the first part of your suggestion mean that with each page of the article, the article_id would have to have the same value. Is there a good way to get around this?
Do you intend for this to be a "flat" table? It seems to me that list tables might be helpful. For example, a KeyWord table that can be linked to your article table via an intermediary table. Perhaps the same with authors (if there is more than one author per article).
...if I had, for example, all the 4 pages of the article in the article table I may have a problem with the article_id field because at the moment it is the primary key of the article table.
well, like i said earlier (and i repeat for emphasis now), it might be a better idea not to have "pages" at all, just articles -- one article, one row -- then your front end could decide where to split an article into pages
but if you must have pages, yes, then the combination (article_id,page_no) would be the primary key