StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If the set of sample attributes was relatively static then the pragmatic solution that would make your life easier in the long run would be <strong>option #2</strong> - these are all attributes of a SAMPLE so they should all be in the same table.</p> <p>Ok - you <em>could</em> put together a nice object hierarchy of base attributes with various extensions but it would be more trouble than it's worth. Keep it simple. You could always put together a few views of subsets of sample attributes.</p> <p>I would only go for a variant of your option #3 if the list of sample attributes was very dynamic and you needed your users to be able to create their own fields.</p> <p>In terms of implementing <em>dynamic</em> user-defined fields then you might first like to read through Tom Kyte's comments to <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056" rel="nofollow noreferrer">this question</a>. Now, Tom can be pretty insistent in his views but I take from his comments that you have to be <em>very</em> sure that you really need the flexibility for your users to add fields on the fly before you go about doing it. If you really need to do it, then don't create a table for each data type - that's going too far - just store everything in a varchar2 in a standard way and flag each attribute with an appropriate data type.</p> <pre><code>create table sample ( sample_id integer, name varchar2(120 char), constraint pk_sample primary key (sample_id) ); create table attribute ( attribute_id integer, name varchar2(120 char) not null, data_type varchar2(30 char) not null, constraint pk_attribute primary key (attribute_id) ); create table sample_attribute ( sample_id integer, attribute_id integer, value varchar2(4000 char), constraint pk_sample_attribute primary key (sample_id, attribute_id) ); </code></pre> <p>Now... that just looks evil doesn't it? Do you really want to go there?</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