P Kishor wrote:
> On Sun, Mar 15, 2009 at 11:56 AM, P Kishor <punk.k...@gmail.com> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to