P Kishor wrote:
> On Sun, Mar 15, 2009 at 11:56 AM, P Kishor <[email protected]> wrote:
>
>> I am designing a database for carbon modeling. Many of the parameters
>> to be stored in the db have very long names... it is kinda
>> inconvenient to have column names such as
>> 'new_live_wood_Carbon_to_new_total_wood_Carbon', but I hate column
>> names such as 'nlivwdc2ntotwdc'. I may as well just call that column
>> 'a' and then have a lookup table which describes 'a' => 'Ratio of new
>> live wood Carbon to new total wood Carbon' along with possibly other
>> descriptors (some columns are a flag, that is, 0 or 1, while others
>> are integers or fractions, and so on).
>>
>> Any suggestions on how to embed this metadata in the table? Is the
>> following the best way --
>>
>> CREATE TABLE foo (
>> a REAL, -- Ratio of new live wood Carbon to new total wood Carbon
>> b INTEGER -- (minimum interval between disturbances)
>> );
>>
>> And, how do I store metadata about the table itself? The following
>> doesn't stick in the schema --
>>
>> -- The table 'foo' is blah blah
>> CREATE TABLE foo ();
>>
>>
>
>
> The following seems to work
>
> CREATE TABLE foo (
> -----------------------
> -- the table foo is about blah blah
> -----------------------
> a, -- blah
> b -- blah
> );
>
> The other question about column metadata is still looking for a suggestion.
>
>
>
>
>
One way to handle columns with restricted data type is to use a "domain
table", basically a table that stores the allowed values for a column in
another table. The values stored in the first table are the keys to rows
in the domain table and referential integrity checks can be used to
ensure only legal values are stored. The domain table can also hold
other "meta" data about the column, such as a text description of the
meaning of the value.
Simple cases such as 0 or 1 can be handled with a check constraint on
the table column which may be simpler to read.
create table t (
id integer primary key,
flag integer check (flag = 0 or flag = 1),
size integer references sizes(id),
data text
);
create table sizes(
id integer primary key,
size text
);
insert into sizes values (1, 'small');
insert into sizes values (2, 'medium');
insert into sizes values (3, 'large');
insert into t values (null, 1, (select id from sizes where size =
'medium'));
If you create referential integrity triggers on the database, those
triggers will prevent inserting illegal values into your table.
You can then display the restricted data values by joining the main
table with the domain table(s), possibly using a view.
create view tv as
select id, flag, s.size as size, data
from t join sizes as s on t.size=s.id;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
