StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The design pattern you're hinting at is called <a href="http://martinfowler.com/eaaCatalog/serializedLOB.html" rel="nofollow noreferrer">Serialized LOB</a>. You can store some data in the conventional way (as columns) for attributes that are the same for every entry. For attributes that are variable, format them as XML or MarkDown or whatever you want, and store it in a TEXT BLOB.</p> <p>Of course you lose the ability to use SQL expressions to query individual elements within the BLOB. Anything you need to use in searching or sorting should be in conventional columns.</p> <hr> <p>Re comment: If your text blob is in XML format, you could search it with <a href="http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html" rel="nofollow noreferrer">XML functions</a> supported by MySQL 5.1 and later. But this cannot benefit from an index, so it's going to result in very slow searches. </p> <p>The same is true if you try to use <code>LIKE</code> or <code>RLIKE</code> with wildcards. Without using an index, searches will result in full table-scans.</p> <p>You could also try to use a MySQL <a href="http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html" rel="nofollow noreferrer">FULLTEXT</a> index, but this isn't a good solution for searching XML data, because it won't be able to tell the difference between text content and XML tag names and XML attributes.</p> <p>So just use conventional columns for any fields you want to search or sort by. You'll be happier that way.</p> <hr> <p>Re question: If your documents really require variable structure, you have few choices. When used properly, SQL assumes that every row has the same structure (that is, columns). Your alternatives are:</p> <ul> <li><a href="http://martinfowler.com/eaaCatalog/singleTableInheritance.html" rel="nofollow noreferrer">Single Table Inheritance</a> or <a href="http://martinfowler.com/eaaCatalog/concreteTableInheritance.html" rel="nofollow noreferrer">Concrete Table Inheritance</a> or <a href="http://martinfowler.com/eaaCatalog/classTableInheritance.html" rel="nofollow noreferrer">Class Table Inheritance</a></li> <li><a href="http://martinfowler.com/eaaCatalog/serializedLOB.html" rel="nofollow noreferrer">Serialized LOB</a></li> <li><a href="http://nosql-databases.org/" rel="nofollow noreferrer">Non-relational databases</a></li> </ul> <p>Some people resort to an antipattern called Entity-Attribute-Value (EAV) to store variable attributes, but honestly, don't go there. For a story about how bad this can go wrong, read this article: <a href="http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/" rel="nofollow noreferrer">Bad CaRMa</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload