Hi folks,
i am a 3rd year IT student and for my final year project i am creating a web based multimedia database cms. This will act in a similar way to flickr or nuke but with subject specific additions. For the scripting side of things i have choosen PHP given my knowledge of this language and it's ease of use. However for the backend and datastorage i have the choice of mysql (which i am familiar with) or XML (which i am not familiar with).
I have seen many (MANY!) cms's built using php/mysql, but on initial searching there seem to be only a few based on php/XML.
Could someone give me a bullet list of pros and cons of using XML over mysql for my application? Is there a resounding reason not to use mysql for modern data storage solutions? I'm thinking long term data storage but also the ease of displaying that data easily on a website. Plus efficently searching the data (and there would likely be lots of data to search so my solution has to scale well), as well as saving multimedia files including text as well as still images (png), video clips, sound clips etc etc. Is XML capable of this sort of data storage? Perhaps some sort of MIME encoding? I was thinking that in mysql i would use blobs but am not familiar with XML's methods.
Sorry for the ramble, any insights would be useful. Thanks in advance
mysql or xml for cms?
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
They both offer advantages and disadvantages.
XML by it's open nature is more portable, however SQL is flexible and portable enough to avoid this disscussion for the most part.
My primary interest in XML is it's intrinsic support for structure. Most data is best stored in a structured hierarchial fashion, which XML lends it self nicely, better than SQL. So in that regard, XML is better for storing many types of data. Indexing hierarchial structures is at times just as fast and sometimes faster than linear relationships are capable of. For the sake of brevity I won't get into how or why this works.
SQL however is more capable or flexible for expressing complicated relationships. XML, like most OOP languages of today only allow single parent hierarchies which when used to model real life organisms fail because almost(??) every living organism stems from two parents, not one.
Really it depends on what kind of objects your modeling. Speed isn't really a consideration as either can be tweaked to perform as fast as the other with XML maybe having some advantages when indexing hierarchial data.
A CMS (content management system) would typically resemble something of a operating systems file structure. Each article would like only ever need one parent, so an OOP/XML architecture would suffice, but so would a SQL database, just not without some additional wiring.
If your article structure is linear, than SQL is just fine.
For most scenarios I think either would work fine, in fact SQL likely wins because of it's wide spread adoption. Only if you absolutely needed XML would it make sense to use it.
For instance if you had a massive (billions) amount of articles within deepely nested structures, it might then make sense to use XML or an OODBMS as indexing in those cases would likely be faster.
XML is also king of portability, so if you had users editing articles using desktop editors and others using cell phones and perhaps others using web pages, then in this case XML likely offers the more flexible solution.
XML is not as secure as a RDBMS so it has that disadvantage.
Portability: XML Wins
Scalability: Arguably they are the same, but it depends on the data being modeled, engines, etc.
Security: SQL Wins
Flexibility: SQL Wins
Again, for the most part SQL is just fine and works, unless you absolutely need XML, likely best to stick with RDBMS
Cheers
XML by it's open nature is more portable, however SQL is flexible and portable enough to avoid this disscussion for the most part.
My primary interest in XML is it's intrinsic support for structure. Most data is best stored in a structured hierarchial fashion, which XML lends it self nicely, better than SQL. So in that regard, XML is better for storing many types of data. Indexing hierarchial structures is at times just as fast and sometimes faster than linear relationships are capable of. For the sake of brevity I won't get into how or why this works.
SQL however is more capable or flexible for expressing complicated relationships. XML, like most OOP languages of today only allow single parent hierarchies which when used to model real life organisms fail because almost(??) every living organism stems from two parents, not one.
Really it depends on what kind of objects your modeling. Speed isn't really a consideration as either can be tweaked to perform as fast as the other with XML maybe having some advantages when indexing hierarchial data.
A CMS (content management system) would typically resemble something of a operating systems file structure. Each article would like only ever need one parent, so an OOP/XML architecture would suffice, but so would a SQL database, just not without some additional wiring.
If your article structure is linear, than SQL is just fine.
For most scenarios I think either would work fine, in fact SQL likely wins because of it's wide spread adoption. Only if you absolutely needed XML would it make sense to use it.
For instance if you had a massive (billions) amount of articles within deepely nested structures, it might then make sense to use XML or an OODBMS as indexing in those cases would likely be faster.
XML is also king of portability, so if you had users editing articles using desktop editors and others using cell phones and perhaps others using web pages, then in this case XML likely offers the more flexible solution.
XML is not as secure as a RDBMS so it has that disadvantage.
Portability: XML Wins
Scalability: Arguably they are the same, but it depends on the data being modeled, engines, etc.
Security: SQL Wins
Flexibility: SQL Wins
Again, for the most part SQL is just fine and works, unless you absolutely need XML, likely best to stick with RDBMS
Cheers