Making Intelligent Use of ARCHIBUS Database Keys

Hi All,

My name is Todd Forsyth.  I’m the Technical Lead at RSC LLC.  We specialize in installing, hosting and managing ARCHIBUS IWMS systems.  This is one of a series of posts on how to make that process easier if you’re doing something similar yourself.

I started out my technical life not as an ARCHIBUS developer, but as a technology consultant at a major technology consulting firm.  In this role, I both installed packaged applications (including Oracle Applications and Kana Customer Service) and developed web-based applications and data warehouses from scratch.

One of the biggest surprises for me in digging into the ARCHIBUS application is how it uses database keys.  And when I say “keys,” I mean the unique values that identify a record.   Things like the Work Request Code (wr_id) in the Work Requests (wr) table.

I’ll talk about what I found surprising, the pros and cons I find with the ARCHIBUS approach, and give some practical advice on what you need to do to use these database keys intelligently.

First a little background on database keys.  As I said, the key is a single value that uniquely identifies each record.  Things like a Work Request Code, or an Employee ID, or a Room Number in ARCHIBUS.  In ADDITION to identifying records in its primary table, these values are ALSO used to tie a lot of data tables in your database together behind the scenes.  For example, each employee record in the employee table also contains columns for Room Code, Floor Code and Building Code, so you’ll know where the employee sits.

Before joining RSC, all the applications and data warehouses I’d had a deep look at used what are called “surrogate keys.”  That is, the key was always a made-up value, most often an integer number that had no intrinsic information stored in it.   In such a system an employee records might have an employee_id value of ‘291716’.    This number wouldn’t have anything to do with the real employee.  It wouldn’t be their employee number or badge number, and CERTAINLY not their name.  To find those, you’d have to use this number to look them up in the employee table.

By contrast, ARCHIBUS uses what are called “natural keys,” where the keys are meant to contain some information about the record they are the key to.  An employee_id in such a system might be something like “TFORSYTH,” telling us something about the employee’s name.

It turns out that there are at least a couple of different schools of thought about the “right” way to build application databases.  Some say that all primary keys should be surrogate, or arbitrary value, keys.  Others find good reasons to use natural, meaningful data as a key.  Let’s take a closer look at why both groups think they’re right:

The Pros and Cons of Surrogate, or “Arbitrary Value” Keys

This is the world I was used to; the way Oracle Applications, and SAP, and PeopleSoft all handle their databases.  There are strengths to this approach, and challenges:

Pros
  • The key has no intelligence built into it. Meaning you cannot derive any meaning, or relationship between the surrogate key and the rest of the data columns in a row.  If things change in a way which would require you to update the basic information about a record (say you want to re-number all your rooms, or institute a new employee numbering system), this can be done without changing this value in a host of tables.    You simply change the meaningful value in the primary or “home” table where that value lives.  You could just update the room number in the room table, for example.  This sure makes it easier when these values need to be changed.
  • Surrogate keys are usually integers, which only require 4 bytes to store, so the keys, and any database indexes which use them, will be smaller in size than their natural key counterparts.  All a fancy way of saying that big queries with lots of tables run faster with surrogate keys.
 Cons
  • If foreign key tables use surrogate keys then you will be required to have a join to retrieve the real foreign key value.  (Meaning if you store the room id where an employee is seated in the employee table, you ALWAYS have to look up the “real” room number in the room table).  You wouldn’t have to do this with natural keys.  Some meaningful data (like a room number) would already be right there.  If you needed to dig deeper, though, like getting the room name, you’d STILL need to go back to the room table to get it.
  • So surrogate keys are not useful when searching for data, since they have no meaning.  You have to go back to the primary table.

 The Pros and Cons of Natural or “Real Data” Keys

This is the type of key structure ARCHIBUS uses, and all of these strengths and weaknesses are those that ARCHIBUS is subject to:

Pros
  • Since the keys store some useful data, you will usually require less joins/tables when writing a query.  I’ve definitely found this to be true in ARCHBUS.  Often you don’t need to join to the building or employee table; it’s enough that you know the key value stored in the local table you’re looking at.
  • Searches are easier because natural keys have meaning, and you don’t need to do so many joins to get to something meaningful
Cons
  • Much more work is required to change the value of the key.  Changing a Building Code, or “bl_id” value, for example, requires that ARCHIBUS look in over 100 tables where this key might be stored.  The ARCHIBUS applications are smart enough to make this change, but a developer who builds on top of ARCHIBUS must constantly keep this in mind, especially if these keys are being stored in custom tables or fields, of if such value updates happen OUTSIDE of ARCHIBUS logic (which can happen when those key values come in from outside, as through an Employee Sync with an HR system.)
  • Your primary key columns, and any indexes that looks at them will be larger because natural keys are usually strings, which take more space to store than “arbitrary” integers.  Larger key columns and indexes mean queries that take longer to run.  However, since ARCHIBUS databases are typically small in size, this isn’t usually a major concern.  Some tuning may need to be done as the database grows, however.

Using ARCHIBUS Keys Intelligently

Now, while the above has been an interesting exercise in the theoretical, it’s not a choice we really get to make in ARCHIBUS.  ARCHIBUS uses Natural Keys.  That said, this implies a few things you need to keep in mind in setting ARCHIBUS up:

  • Don’t pretend you have Surrogate Keys – I’ve known clients who are absolutely SURE surrogate keys are the way to go, even in ARCHIBUS.  They want to assign ONLY numbers to their building, employee, or department keys (or “Codes”.)  This is counter-productive for a couple of reasons:
    • ARCHIBUS exposes the keys in places many other applications expose name or description fields.  So if you want to have a clue what you’re looking at (who is this employee?  Which building is this?  Is this department Accounting or Legal?), you NEED to give this data some meaning.
    • You will find yourself customizing nearly EVERY form you use regularly to go look up the meaningful data you need from its primary table, where it’s being stored in the Description or Name field. This is a recipe for disaster when doing an upgrade.  Save your customizations for things that really matter.
  • Giving your data some meaning doesn’t mean keys are free-form text fields.  You need to be VERY careful about what you put in here.  You might want to think about exposing your potential key value schemes to these four tests before calling them final:
    • Is the primary key unique?  – My example above of using first initial, last name (TFORSYTH) as a primary key for the employee table is a good example of scheme that DOESN’T pass this test.  Adding the employee number might solve this:  (TFORSYTH F2314)
    • Does it apply to all rows?  – Are there some data points that just doesn’t fit the scheme?  What if you have an employee with a last name like “Wolfeschlegelsteinhausenbergerdorff?”  ‘Probably a bad idea to use the WHOLE last name.  Maybe X characters?
    • Is it minimal?  Remember, big size is one of the problems with natural keys.  Keep your values SHORT.  ARCHIBUS does a good job of enforcing this through their default key field sizes.
    • Is it stable over time?  This one is the real kicker.  Can you GUARANTEE that these values will never change?  Of COURSE you can’t.  But if they change ALL THE TIME, you probably need to look harder for something to use as a key.

I hope you’ve found this look at ARCHIBUS keys useful and informative, and that it can help inform the way you set up and use them in your system.


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

Three Open Source JavaScript UI Tools

This week we have another article courtesy of Todd, our Technical Lead at Robert Stephen Consulting, LLC.  Enjoy!

At RSC, we specialize in installing, hosting and managing ARCHIBUS IWMS systems.  We also write our own software to enhance ARCHIBUS capabilities.  These days, more and more of that software is written in javascript and HTML5.

In this post, I’m going to briefly talk about three open source tools I find make the job of building and managing a javascript user interface simpler.  Here’s the list:

  1. jQuery
    • A set of tools that make it easier to work with DOM objects (among other things)
  2. Block UI
    • A handy tool to “freeze” the UI while something important is happening
  3. mustache
    • A templating tool for easily rendering the contents of arrays and variables

And now a more detailed description of each, and why each is useful:

1.    jQuery

Link:    https://jquery.com/
I’ve been using jQuery for about 6 months now, and know I’m only scratching the surface of what it can do.  I use it to easily find and manipulate DOM (Document Object Model) objects (“find me an object with this ID”), and to easily use ajax to send data to and receive data from the server.  jQuery does a LOT of things:  HTML/DOM manipulation, CSS manipulation, HTML event methods, Effects and animations and Ajax.  It also does some cool things with storing and retrieving data.   It’s probably the most popular javascript library there is, and because it’s open-source, this means there are ALSO a lot of plugins that extend what it can do.  Here’s a great link to get starting finding these:

https://learn.jquery.com/plugins/finding-evaluating-plugins/

Also, a quick list of the best reasons to use jQuery yourself:

  • jQuery is light; the code needed to run it is small, so it won’t significantly increase the time for server response.
  • Even with all the power it brings to bear, the jQuery library is open source and free.
  • It’s easy to learn; if you can program javascript already, this will just look like a toolbox of easily-mastered shortcuts to you.
  • It’s cross-platform, running on all major browsers and on computers and mobile devices.

To be open about it, there IS some criticism of jQuery by javascript purists, particularly in the way it handles an important component of asynchronous operations called “promises.”  Given the problems it solves, these are problems I’m more than willing to live with.

2.    BlockUI

Link:    http://malsup.com/jquery/block/

Speaking of jQuery plugins, here’s one now!  BlockUI  will “freeze” a web page by fading it out, showing a spinning “busy” indicator, and a custom message.  It also prevents user interactions with the page.  This can be very handy when you’re doing things like loading a lot of data in the background, or busy building the page objects, and the code to handle what pushing a button will do.

Once you include the library, blocking the UI, even with a custom message, is pretty easy.  Here’s the code to do it:

$.blockUI({ message: ‘

Just a moment…

‘ });

Unblocking is just as simple.

I was impressed with this plugin because I’m trying to blend the old and new worlds of forms and javascript for the short term, until I get a complicated form-based UI re-written in modern Responsive Design fashion.  BlockUI (in combination with jQuery) allowed me to handle this gracefully, freezing the whole frameset, instead of worrying about each individual “page” within it.

3.    mustache

Link:    https://mustache.github.io/
Do you have a lot of data (arrays, variables, objects) that you’re trying to show your user as HTML? Tired of writing loops to get you through the arrays, or “if” tests to decide whether particular things should be shown at all?  mustache is a neat little library that allow you to create what the creators call “Logic-less templates.”  You just include the mustache library, define your HTML as a template, add some specially-formatted tags, then populate the variables, and tell mustache to render the template using the variables.  Simple goodness.  A quick example, so you can see how easy it is:

A typical Mustache template:

 

Hello {{name}}
You have just won {{value}}
dollars!
{{#in_ca}}
Well, {{taxed_value}} dollars,
after taxes.
{{/in_ca}}
Given the following hash:

 

{“name”:
“Chris”,
“value”: 10000,
“taxed_value”: 10000 – (10000 * 0.4),
“in_ca”: true}
Will produce the following:

 

Hello Chris
You have just won 10000 dollars!
Well, 6000.0 dollars, after taxes.

As you can see, the tags tend to look like this:  “{{name}}”, and the curly brace on its side looks not unlike a mustache.  So the name is pretty easy to understand.


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

Four Free Tools to Manage ARCHIBUS in a Windows Environment

This week’s article is brought to you by Todd.  Todd is the Technical Lead at Robert Stephen Consulting, LLC.  RSC specializes in installing, hosting and managing ARCHIBUS IWMS systems.  The article Todd is sharing with us today is one of a series of posts on how to make that process easier.
Most of the ARCHIBUS Environments we build are on the Microsoft Windows Server platform.  In this post, Todd briefly shares some phenomenal tips about four FREE (or nearly free) tools he finds make the job of managing ARCHIBUS in a Windows environments much easier. The four tools include:

 

      1. Notepad ++
        • A text editor with some very nice extra features
      2. Agent Ransack
        • A file search utility
      3. WinDirStat 
        • A filesystem management utility
      4. PortQury
        • A Microsoft command line utility for checking the status of TCP/UDP ports

1. Notepad++

Link:       https://notepad-plus-plus.org/
Notepad++ is mostly a text editor.  I know what you’re thinking:  “Well, I already HAVE a text editor.  I don’t really need another one.  Mine works just fine!”

Yes, but can your text editor do these things?:

 

  • Open a “live” file (like a log) without getting in way of other processes accessing it. That way, you can look at the log in real time, without making a copy.
  • Search for all the instances of a text string in all the files in a directory you name, and allow you to quickly jump to that string in the file where it’s located.
  • Mark up source code, and help you find missing parentheses or curly braces.  I find the XML, Javascript, Java and HTML options very handy in dealing with ARCHIBUS custom views and their supporting workflow rules.  It’s also quite nice to be able to collapse expand sections of code (like an “if” block) to see how they fit into the larger code flow.
Don’t get me wrong, this isn’t an Integrated Development Environment (or IDE).  It doesn’t require that kind of setup or learning curve. But it is a very powerful tool that a sometimes-programmer or troubleshooter can use to make their work a lot easier.

 

I find this very useful when I need to change the way a particular variable is used, or to change all instances of a function call, for example.  It’s very easy to find them all quickly, and to work my way through the search results, updating as I go.

 

Notepad++ is also more forgiving than other editors I’ve used.  Most will let you “undo” up to the last save.  Notepad++ will let you undo up to the time you opened the file.

2. Agent Ransack

Agent Ransack is a file search utility.  And it’s ALMOST free.  At $10/copy, less for larger numbers, it’s definitely well worth the normal investment. What it does isn’t complicated, but it is powerful.  Agent Ransack lets you:

 

  • Search for a filename or directory / folder name
  • Search for text inside the file, including using regular expressions (basically wildcard searches)
  • Specify the time the file was saved, created or last accessed

…all without the files / filesystem in process needing to be indexed, as is needed for Windows Filemanager Search.  And it’s quite fast.

I find these capabilities very useful in finding a file or view in ARCHIBUS’ very complex schema. WebCentral will give you the filename, but won’t tell you the location.  Agent Ransack solves that problem.

3. WinDirStat

Link:       https://windirstat.info/

It seems that no matter how big your drives are, they’re still not big enough.  How many times have you found that your drive is ALMOST full, then had a very difficult time freeing up the space you need?  It seems like the hardest task is just FINDING the offending file or application.  You have click on the properties of each folder to see its size, then look inside the biggest, look at all the folders there to find where the space is being used up, ad infinitum.

WinDirStat does all of that for you, and builds a cool and very intuitive visual representation of the things on your drive that lets you INSTANTLY see if you have a very large file gumming up the works.  Often, it’s an old log file that can be deleted instantly once you know where to find it, and your drive is once again healthy and trim.

4. PortQry

OK, this one doesn’t look cool AT ALL.  It’s a Microsoft-written command-line utility without even an install package.  But it’s SO useful.  One of the most common problems in setting up or troubleshooting an ARCHIBUS environment is for components of the application not to connect as they should.  But is the problem with the network or firewall, or the database server setup, or with the Project setup on the ARCHIBUS side?  One of the toughest things to see easily is whether the network port you’re trying to connect to is open and listening, or filtered in some way. This is what PortQry does.  With a single command line query, you can find that out in real time, and stop fiddling with all the components hoping something will work.

Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.