StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What you're describing is often called "Entity-Attribute-Value," and sometimes described as "mixing data and metadata." That is, the names of attributes (fields) are stored as strings (data). </p> <p>This leads to a bunch of complex problems like making sure each form instance includes the same set of fields, or making sure mandatory fields are filled out (the equivalent of NOT NULL in a conventional table).</p> <p>You asked how best to do this with a relational database. In a relational database, you should use metadata for metadata. In this case, it means creating new table(s) for each form, and use columns for form fields. So your form definition is simply the table metadata, and one form instance is one row in that table. If you support forms with multi-valued answers (e.g. checkboxes), you need dependent tables too.</p> <p>This might seem expensive or hard to scale. Probably true. So a relational database might not be the best tool for this job. You mentioned the possibility of XML or YAML; basically some kind of structured file format that you can define ad hoc. You should define a DTD for each client's form, so each form collected can be validated.</p> <p><strong>edit:</strong> If you really need the flexibility of EAV in your application, that's fine, there are usually circumstances that justify breaking the rules. Just be aware of the extra work it takes, and plan for it in your development schedule and as you scale your server to handle the load. Also see another <a href="https://stackoverflow.com/questions/192892/performance-of-large-eavopen-schema-systems-on-sql-server#231681">answer of mine about EAV</a> on StackOverflow.</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