[WF-Infra] eidetic sql - please review

Mike Taylor mike at utisolutions.com
Thu Oct 4 09:22:45 PDT 2001


Here is another pass at the structures for eidetic.  The type
definitions at the top are going to be replaced with a script that will
run a series of s// to generate the target rdbms format.  Another s//
series will also insert the required fields so I did not note them.
 
Please send me notes as to any item that you feel needs filling-out or
is missing.
 
Thanks,
bear
 
eidetic.sql:
 
create datatype T_idFKey      integer not null ;
create datatype T_idKey       integer not null ;
create datatype T_email       varchar(50)  ;
create datatype T_URL         varchar(255)  ;
create datatype T_Phone       varchar(25)  ;
create datatype T_ShortText   varchar(50)  ;
create datatype T_LongText    varchar(255)  ;
create datatype T_Boolean     smallint  ;
 
create table user
(
    id              T_idKey               not null,
    UserID          T_ShortText                   ,
    Name            T_ShortText                   ,
    SurName         T_ShortText                   ,
    Organization    T_ShortText                   ,
    DisplayName     T_ShortText                   ,
    Email1          T_email                       ,
    Email2          T_email                       ,
    URL             T_URL                         ,
    PublicKey       T_LongText                    ,
    SSHIdentity1    T_ShortText                   ,
    SSHIdentity2    T_ShortText                   ,
    Mobile          T_Phone                       ,
    Work            T_Phone                       ,
    Pager           T_Phone                       ,
    Address1        T_ShortText                   ,
    Address2        T_ShortText                   ,
    City            T_email                       ,
    State           varchar(20)                   ,
    PostalCode      varchar(20)                   ,
    Country         varchar(20)                   ,
    Timezone        varchar(10)                   ,
    primary key (id)
);
 
create table table_info
(
    id              T_idKey               not null,
    Name            T_ShortText                   ,
    Description     T_LongText                    ,
    Owner           T_idFKey              not null,
    Module          T_idFKey              not null,
    primary key (id)
);
 
#
# all fields for any defined table
#
#   Table is foreign key to table_info
#   Field is foreign key to field_info
#
 
create table table_fields
(
    id              T_idKey               not null,
    Table           T_idFKey              not null,
    Field           T_idFKey              not null,
    primary key (id)
);
 
#
# field definitions
#   setup so that only one definition is needed
#   but not required
#
 
create table field_info
(
    id              T_idKey               not null,
    FieldType       T_idFKey              not null,
    Description     T_LongText                    ,
    Prompt          T_ShortText                   ,
    AllowNull       T_Boolean                     ,
    isKey           T_Boolean                     ,
    ValidationRule  T_LongText                    ,
    primary key (id)
);
 
#
# all known field types
#
 
create table field_type
(
    id              T_idKey               not null,
    Description     T_LongText                    ,
    MatchPattern    T_ShortText                   ,
    primary key (id)
);
 
#
# all known rdbms types
#
 
create table fieldtype_map
(
    id              T_idKey               not null,
    DB              T_idFKey              not null,
    Description     T_LongText                    ,
    primary key (id)
);
 
#
# field type to rdbms type map
#
 
create table type_map
(
    id              T_idKey               not null,
    FieldType       T_idFKey              not null,
    DB              T_idFKey              not null,
    Description     T_LongText                    ,
    primary key (id)
);
 
#
# host definitions
#
 
create table host
(
    id              T_idKey               not null,
    OS              T_idFKey              not null,
    Name            T_ShortText                   ,
    Description     T_LongText                    ,
    CPU             integer                       ,
    RAM             integer                       ,
    Disk            integer                       ,
    Spindles        integer                       ,
    Platform        integer                       ,
    Networks        integer                       ,
    primary key (id)
);
 
#
# operating systems
#
create table os_type
(
    id              T_idKey               not null,
    Description     T_LongText                    ,
    Version         T_ShortText                   ,
    primary key (id)
);
 
#
# articles
#
# summary information for all articles
#
 
create table article
(
    id              T_idKey               not null,
    Title           T_LongText                    ,
    Abstract        T_LongText                    ,
    Author          T_idFKey              not null,
    Topic           T_ShortText                   ,
    Type            T_idFKey              not null,
    URL             T_LongText                    ,
    Parent          T_idFKey              not null,
    primary key (id)
);
 
#
# all known article types
#
 
create table article_type
(
    id              T_idKey               not null,
    Description     T_LongText                    ,
    Format          T_ShortText                   ,
    primary key (id)
);
 
#
# summary information for all events
#
 
create table event
{
    id              T_idKey               not null,
    Type            T_idFKey              not null,
    Coordinator     T_idFKey              not null,
    Description     T_LongText                    ,
    Location        T_ShortText                   ,
    StartDate       datetime                      ,
    EndDate         datetime                      ,
    StartTime       datetime                      ,
    EndTime         datetime                      ,
    primary key (id)
};
 
#
# all known event types
#
 
create table event_type
(
    id              T_idKey               not null,
    Description     T_LongText                    ,
    primary key (id)
);
 
#
# summary infornation for all tasks
#
 
create table task
{
    id              T_idKey               not null,
    Type            T_idFKey              not null,
    Coordinator     T_idFKey              not null,
    Description     T_LongText                    ,
    Summary         T_LongText                    ,
    primary key (id)
};
 
#
# all known task types
#
 
create table tast_type
{
    id              T_idKey               not null,
    Description     T_LongText                    ,
    primary key (id)
};
 
#
# task to user map
#
 
create table task_map
{
    id              T_idKey               not null,
    Task            T_idFKey              not null,
    User            T_idFKey              not null,
    Role            T_idKey               not null,
    primary key (id)
};
 
#
# all known task roles
#
 
create table role_type
{
    id              T_idKey               not null,
    Description     T_LongText                    ,
    primary key (id)
};
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: /archive/attachments/20011004/fb2f3c53/attachment.htm


More information about the Infra mailing list