The Tribal SITS database doesn’t have the worst data model I’ve ever used but it’s still not the easiest to work with. The table and column names are logically organised but most are just four characters long. So they’re effectively codes the meaning of which is not always clear. I typically build a view layer for the tables I use frequently to add in more verbose table and column names as well as adding in all the lookup tables of which there are hundreds. Another issue is there are no foreign key constraints on the database. Aside from the fact that referential integrity is not enforced this makes finding table relationships difficult. On the up side SITS does maintain its own data dictionary which helps with these and some other issues.
SITS Data Dictionary Tables
The data dictionary tables I’ll look at are:
men_ent
The entities table contains all the SITS entities. This is close to being list of database tables except that men_ent may contain multiple entities for the same underlying table. This is because an entity can exist in one of several dictionaries (men_dct) which define the area of the application (SRS, CAMS etc) that the entity is used in. So the entity STU exists in both CAMS and SRS dictionaries but there is only one table (INS_STU). As far as I can tell there is no way of identifying which entity is the “definitive” one but it’s normally obvious from the context. The entity record does not have the table name stored directly – it’s the concatenation of ent_prfx and ent_code.
men_fld
The fields table contains all fields on all entities. Again this is close to all fields on all database tables but there are a handful of entity fields not stored on the database table. These are presumably calculated fields and are indicated by the field fld_indb = N. Best of all the fields fld_dct1, fld_ent1, fld_fld1 identify the lookup table and its primary key field for any lookup fields. This is not entirely reliable but is still incredibly useful in tracking foreign key relationships from a table.
men_udf
The udf table provides details of how user defined fields have been configured. In the fields table each UDF field will look something like CAP_UDF1 – User Defined Field 01. By looking up the field in men_udf we can get the configured field name and other information about how the UDF is configured.
SITS Data Model View
Using these dictionary tables I can produce a view to get details of all table fields, UDF field details and lookup table details and even output joins to those lookup tables:
create view vw_desc_sits_entity as select d.dct_code dictionary , e.ent_prfx + e.ent_code table_name , e.ent_name , f.fld_seqn , f.fld_code column_name , f.fld_name description , f.fld_indx indexed , f.fld_mand mandatory , f.fld_type field_type , u.udf_name , u.udf_type , f.fld_indb in_database , le.ent_prfx + le.ent_code lookup_table , lf.fld_code lookup_PK , 'left join ' + le.ent_prfx + le.ent_code + ' ' + le.ent_code + ' on ' + e.ent_prfx + e.ent_code + '.' + f.fld_code + ' = ' + le.ent_code + '.' + lf.fld_code lookup_join , u.udf_prof , f.fld_help help_text from men_dct d join men_ent e on d.dct_code = e.ent_dctc join men_fld f on e.ent_dctc = f.fld_dctc and e.ent_code = f.fld_entc left join men_fld lf on lf.fld_dctc = f.fld_dct1 and lf.fld_entc = f.fld_ent1 and lf.fld_code = f.fld_fld1 left join dbo.men_ent le on lf.fld_dctc = le.ent_dctc and lf.fld_entc = le.ent_code left join men_udf u on f.fld_dctc = u.udf_dctc and f.fld_entc = u.udf_entc and f.fld_code = u.udf_fldc
I’ve also added a stored procedure to query this view. Specifying the dictionary and table name here will just output all field details for the table. However leave the table blank and specify the lookup table name and it will return all tables that reference that lookup table which I’ve also found very useful when investigating the SITS Data Model:
ALTER procedure desc_sits_entity @dictionary varchar(10) , @table_name varchar(20) = null , @lookup_table_name varchar(20) = null as select * from vw_desc_sits_entity where dictionary = @dictionary and (table_name = @table_name or @table_name is null) and (lookup_table = @lookup_table_name or @lookup_table_name is null) order by table_name , fld_seqn
One use I’ve put this data model view to is to auto generate views for all the major SITS tables to provide a better querying environment.