Business Issues Regarding Background Data

In terms of business data flow, you want to make certain that all staff enters data in terms of roll up categories that are consistent and that are relevant to your business. If some staff use one set of account codes, and others use a different account code convention, the data from the two groups will not roll up to a coherent picture of your facility.

In terms of database consistency, you also want to define these validating codes and make them stick, because once you assign items to a particular category, you only want to change that category with care. For instance, suppose you define a building code value, and then assign floors, rooms, leases, suites, equipment, and so forth to this code. Now if you change this building code, you will also have to cascade this change to all of the other tables of information.

Technical Issues Regarding Background Data

Another reason is technical. By default, Archibus allows NULL values in many fields so that you can create records without having to completely specify their relationships all up front. For instance, if you have a piece of equipment that is ordered but does not yet have a location, you can leave its building, floor, and room codes blank. If it is rooftop equipment, you can fill in the building and floor yet leave the room blank (since this equipment has no room).

With these NULL or blank values allowed, the different database servers let certain conditions through their constraints. Oracle database servers do not restrict entry of multi-part foreign key values when an earlier part of the foreign key has not been entered. For example:

• When entering a Department value for a room, if a Division value has not been entered for that room then it is possible to enter an invalid Department value for that room.
• When entering a room or floor value for an employee, if a building value has not been entered for that employee then it is possible to enter an invalid floor or room for that employee.
• Similarly when entering a city code in a record, if a state code has not been entered then it is possible to enter an invalid city code.
• The behavior will be similar when entering a Department, Floor, Room, or any other latter parts of a multi-part foreign key in any table that has these fields as foreign keys.)

In Microsoft SQL Server, if you omit the last key field value in a composite foreign key, an SQL Server constraint will reject the UPDATE statement, even if that field allows NULL values. For instance, if in the Buildings table you fill a State Code, but not a City Code, SQL Server will reject the change. For this reason, Archibus’s default constraints are implemented as triggers so that the server can reject bad selections but still allow NULL values.

The consequence is that if you make a change to a multi-part primary key, the server allows the change even if that value has dependent records. For instance, if you rename a City Code, the code will not be renamed in the dependent Buildings table.

If you wish to avoid these cascading issues altogether, have your database administrator replace the default constraints. Again, the consequence is that these multi-part keys will always require values for every record; you cannot leave a value blank.