Auto Create Views from the Tribal SITS data dictionary

As I’ve discussed the Tribal SITS Data Model does not have the nicest table and fields names and has a huge number of lookup tables. In this situation I like to build a layer of views over the base tables to provide a friendlier interface to build queries on. Still this is a bunch of work – could I auto create these views from the SITS data dictionary? Short answer: Yes. But they’ll probably need a bit of manual tidying up. That said I can produce working views that will still save a lot of effort. Continue reading “Auto Create Views from the Tribal SITS data dictionary”

Navigating the Tribal SITS Data Model

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. Continue reading “Navigating the Tribal SITS Data Model”