> > > MySQL really wasn't designed for this sort of useage. Your selects are
> > > going to be brute force searches on unidexed data, which is exactly why
> > > they are so slow...
> > >
> > > I would advise you to look into using an XML or XML Enabled RDBMS. There
> > > are a lot of possible considerations here as to what you might want.
> >
> > I would advise you to consider abandoning storing XML in a database
> > entirely, as it completely breaks the idea of database design - namely,
> > "don't store duplicate data". You would probably be much better served by
> > writing code to wrap the actual data in the database with XML - it's quite
> > easy, and a lot less hassle than trying to store XML.
>
> What you have to remember though Ed is that the code you then use to do the
> XML schema to RDBMS schema mapping, your "wrapper" as you call it, becomes a
> really complex and troublesome piece of code which sucks the life out of your
> project very quickly. Thats why there are so many tools out there now to do
> it for you. Its really the same problem you face with object to relational
> mappings. If the project is simple, you can do it by hand, but I've done
> projects where we had upwards of 50 tables, and at that level its a whole
> different ball game!
It's because of you bias towards XML that causes you to think so. For example, I wrote
this tiny Escapade script to generate XML
from fields stored in an email table. While the field names are hard-coded, they could
just as easily have been not - Escapade also
has the ability to pull meta-data from teh database, allowing one to quickly and easily
generate XML. An arbitrary number of tables
would be just as easy to generate XML from . Other scripting languages can do the same:
<QUIET ON>
<?xml version="1.0" standalone="yes"?>
<DBOPEN "mysql", "localhost", "root">
<EMAIL>
<SQL select * from Mail where X_Folder='Inbox' AND X_Personal='Y' order by X_MsgDate
DESC limit 0,5>
<MESSAGE>
<FROMNAME>$X_FromName</FROMNAME>
<SUBJECT>$X_Subject</SUBJECT>
<DATE>$X_MsgDate</DATE>
</MESSAGE>
</SQL>
</EMAIL>
In short XML DOES NOT BELONG IN THE DATABASE! You NEVER mix data and meta-data, and
taht's what you're doing here. Havem't we all
learned by now the lesson of separating content from presentation?
--
Ed Carp, N7EKG http://www.pobox.com/~erc 214/986-5870
Director, Software Development
Escapade Server-Side Scripting Engine Development Team
Pensacola - Dallas - London - Dresden
http://www.squishedmosquito.com
Yeah, yeah, yeah ... sql, mysql, database
