I intend to create a wiki webpage, but it has some few more limitations than regular wikis. The content is completely user-editable, but the structure of the page is determined previously. To make things simpler, let's say that they have a structure of a (title, author, date, chapter1, chapter2,…,chapter10).
So some of them may be empty (like last chapters). I would definitely like the ability to search into it and, if possible, I'd like it to be expandible (to add more fields). The page is edited online with my own editor, so I have full control of the $_POST fields, and each one is in a different variable. So I was thinking about this 2 possible designs:
-
The page is first formatted using XML elements (through PHP) and then inserted into a single text cell in the table. This allows to make backups easier, as well as it's flexible for expansion and does not waste many space resources. The table would also include a modified date and last editor.
-
The mysql table has many columns and each one has a different field. This is not flexible, nor resource-savvy, but it's much easier to operate with.
I was thinking about learning the PHP functions for reading and writing XML and then work with the first option. But I'm new in MYSQL databases (and db design), so I was wondering if this is a good practice before I get on it or there are other ways.
So, what method is considered to be a "good practice", 1st, 2nd or different one?
Best Answer
If the only thing you are storing in the database is the XML data, what is to stop you from storing the file directly on the filesystem and bypassing the overhead of querying the database for the single field?
If backups are your concern, there are methods to backup a directory as well.
Usage of MySQL (or other RDBMS) implies there is a relational aspect to the data you are storing. If there's no relationship, this is probably not the tool you are looking for.