StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To help with Googling, your third option looks a little like the <a href="http://en.wikipedia.org/wiki/Entity-attribute-value_model" rel="nofollow noreferrer">Entity-Attribute-Value</a> pattern, which has been <a href="https://stackoverflow.com/questions/856075/generic-db-table">discussed on StackOverflow before</a> although often critically.</p> <p>As others have suggested, if at all possible (eg: once the system is up and running, few new attributes will appear), you should use your relational database in a conventional manner with tables as types and columns as attributes - your option 1. The initial setup pain will be worth it later as your database gets to work the way it was designed to.</p> <p>Another thing to consider: are you tied to Oracle? If not, there are non-relational databases out there like <a href="http://couchdb.apache.org/" rel="nofollow noreferrer">CouchDB</a> that aren't constrained by up-front schemas in the same way as relational databases are.</p> <hr> <p><strong>Edit</strong>: you've asked about handling new attributes under option 1 (now 1a and 1b in the question)...</p> <ul> <li><p>If option 1 is a suitable solution, there are sufficiently few new attributes that the overhead of altering the database schema to accommodate them is acceptable, so...</p></li> <li><p>you'll be writing database scripts to alter tables and add columns, so the provision of a default value can be handled easily in these scripts.</p></li> </ul> <p>Of the two 1 options (1a, 1b), my personal preference would be concrete table inheritance (1b):</p> <ul> <li>It's the simplest thing that works;</li> <li>It requires fewer joins for any given query;</li> <li>Updates are simpler as you only write to one table (no FK relationship to maintain).</li> </ul> <p>Although either of these first options is a better solution than the others, and there's nothing wrong with the class table inheritance method if that's what you'd prefer. </p> <p><strong>It all comes down to how often genuinely new attributes will appear</strong>. </p> <ul> <li><p>If the answer is "rarely" then the occasional schema update can cope.</p></li> <li><p>If the answer is "a lot" then the relational DB model (which has fixed schemas baked-in) isn't the best tool for the job, so solutions that incorporate it (entity-attribute-value, XML columns and so on) will always seem a little laboured.</p></li> </ul> <p>Good luck, and let us know how you solve this problem - it's a common issue that people run into.</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