StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I've built and used many times what I call <a href="http://chateau-logic.com/content/parameter-enumeration-tables" rel="nofollow noreferrer">Parameter Enumeration Tables</a>. The <a href="http://zxaf.svn.sourceforge.net/viewvc/zxaf/trunk/cbf/params.php?revision=24&view=markup" rel="nofollow noreferrer">module</a> is part of my <a href="http://sourceforge.net/projects/zxaf/" rel="nofollow noreferrer">ZXAF opensource framework</a>.</p> <p>Basic design is simple, you have a <code>Parameters</code> table that has a 1-many relationship with each table that needs a parametrised field. It looks something like this:</p> <p><img src="https://i.stack.imgur.com/HG4ax.png" alt="alt text"></p> <p>Expanding on this to provide a real example, where we are working with a <code>users</code> table that contains a <code>status</code> field. We index and link the field to the <code>params</code> table via a <em>constraint</em> as follows;</p> <pre><code>INDEX `FK_user_status` (`status`), CONSTRAINT `FK_user_status` FOREIGN KEY (`status`) REFERENCES `params` (`id`) ON UPDATE CASCADE ON DELETE CASCADE </code></pre> <p><em>NOTE: I'm using <code>CASCADE</code> here, there are times when you don't want to do this</em></p> <p>This gives us the following schema;</p> <p><img src="https://i.stack.imgur.com/z0jnl.png" alt="alt text"></p> <p>The key concept of this is to allow the database to contain parameterised data that maintains referential integrity, and integrates with a data model within the code. The code is able to find out by querying the database how entities are related, and for example what the valid values for a specific field are.</p> <p><img src="https://i.stack.imgur.com/lGaJ8.png" alt="Parameter Enumeration Tables sample schema"></p> <p>Lastly I want to introduce and explain the concept of <em>Parameters <a href="http://en.wikipedia.org/wiki/Tuple" rel="nofollow noreferrer">Tuples</a></em>. This is another table that allows us to associate a pair of parameters (the Tuple) with a value. This a data neutral way in which we can extend <code>PET</code> provide the lookup and expected values. This is most suited to an extensible model where it is possible to add new enumerations, and yet we need to allow them to contain a value. <em>It is often better to do this with *relationships*</em></p> <p><img src="https://i.stack.imgur.com/U6DQn.png" alt="Parameters schema with Tuples"></p> <p>I'm not in <a href="http://chateau-logic.com/content/why-we-should-not-use-enums-databases" rel="nofollow noreferrer">favour of enums in databases</a>, but this is only my opinion and it may be something that you're happy with.</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