Application Architecture

I doubt you'll ever encounter an organisation with no existing IT infrastructure, so you'll have to make a decision about the applications you write: will they be stand-alone, making no reference to the world around them, or will you try to make them fit in? I've found the latter works best, and here's why.

When you are designing a data model for a database you work hard to normalise it, that is, to make sure that information is not repeated. A simple example is when you need to store information about an employee. You'll have a table with columns for first name, last name, perhaps an employee number, and so on. Rather than have a column for department name and have someone type it in every time, you'll create a second table for departments. That way you won't have the problem of departments called HR, H.R., Human Resources, human resources, and every possible variation that your customers can dream up (and that's a lot, because they are a creative bunch).

So having done all this hard work, why would you want to repeat this information in a second application?

If you do, you've created a problem: someone has to maintain two sets of information (or you've got to build some sort of interface to keep them synchronised). Which version is the right one?

A better way is to think about the best place to hold key corporate information and work with that, so your list of employees should be sourced from the HR system, budgetary information from the Finance system, and so on.

I'm going to explain how you can do this without even knowing what make those systems are. I've use the Oracle eBusiness Suite for my examples, purely because that's what I know best. I've designed a series of adapters that will allow the applications I write to be dropped into a different infrastructure and work with minimal effort.

Hardware manufacturers have done this for years with adapters like USB, but for some reason software developers have been slower to achieve this.

Here's the idea. The HR system holds the master information on employees. Our application needs to know this (let's not worry about why just now), so what we'll do is to create a database view that will expose this information to our application.

Great, with a view we can restrict what information is exposed, so confidential stuff is kept confidential. What could possibly go wrong?

Data integrity, that's what.

If we had a table of employees hard-coded into our own application, we could ensure that no employee record could be deleted if our application was still referencing it. We would have absolute control and our data would be perfect.

But the real world isn't like that. Our application shouldn't be deciding whether an employee record should be deleted, that's why we have an HR system. If our application has a different list then we have created a problem, not solved one. We need to accept that some things are outside our control and live with it.

The real world is a scary place where unexpected things happen. Employee records get created in error, get changed, get deleted accidentally and have to be re-created. Duplicates are entered. People make mistakes. But we find them and fix them, and at the end of the day, our HR system is still the best authority we have on our employees. If it's not, then you need to concentrate on fixing that before you make the problem worse by creating a rival.

So, we have our HR system, and a database view that our application uses to get a list of employees. It's not essential, but wouldn't life be easier if we all shared the same view? Otherwise our poor DBAs are going to be running round creating and maintaining sets of slightly different views for all our applications.

I'd like to propose a standard list of database objects that can be shared. I've struggled with these names, because it is important that they don't conflict with existing database objects that you may already have. This pushed me in the direction of giving the objects complex names. However, I like simplicity and think that we should be able to understand what something is by the name alone, without needing to refer to a lookup to workout the meaning.

So, my proposal for the name of a standard view of employees is STANDARD_EMPLOYEE_VIEW. Not very imaginative, but it stands a good chance of not being already used and yet we can all understand what it holds just from the name alone.

I've defined the following list of views for information that is typically held in existing corporate systems. If you don't already hold some of this information, why not? It doesn't take long to build a little application to allow someone to record this information in a database, because I'm sure they are already maintaining this in at least one spreadsheet.

View Name	Description
STANDARD_EMPLOYEE_VIEW	A list of current employees. Holds employee number, first name, last name, email, telephone number
STANDARD_USER_VIEW	A list of active application users (not just your application, but all applications). This will be different from the employees if your applications are used by others. You may also have employees who never need to use your computer systems. This holds a user id, user name (which may not be their real name), email (which should be real).
STANDARD_ROLE_VIEW	The roles that are used within your application for security or functionality reasons.
STANDARD_USER_ROLE_VIEW	By assigning users to roles you can define who is allowed to do what.
STANDARD_ORGANISATION_VIEW	This is used to define organisations and their structures. It is a hierarchical table, using the parent_id to define it's structure. There may be more than one top-level entry if your systems support multiple organisations. Departments are represented as organisations within the context of the parent organisation, and they can have sub-departments and so on.
STANDARD_PROPERTY_VIEW	A list of the properties that your organisation uses. These could be shops, offices or even land, but very often it's important to know where in the organisation things are, and this is will help us do that.
STANDARD_ACCOUNT_CODE_VIEW	Your organisation is likely to have a list of codes that are applied to financial transactions. Because everything has a cost or a value (ask your accountant if you don't believe me), you may need to tag the things in your application with the right codes.
STANDARD_JOB_VIEW	This may be confusing. HR systems typically have a complex structure for holding information about jobs, positions, posts, assignments, and so on which makes my head spin. Usually only HR really understand all this, most of us just know that we are employed to do a job. It gets a little complicated because sometimes people are assigned to more than one job at a time, and you can have several people doing the same job. So, to keep it simple (and stop my head spinning), this view will have a simple job id that uniquely identifies each instance of a person being assigned to a job. It will have a job title, the worker's employee number, the manager's employee number, and the organisation id that this job belongs to. I've not mapped employees to organisations directly because it's surprisingly common for people to work for more than one department at the same time. The real world is a confusing place.
STANDARD_FILE_REFERENCE_VIEW	Everything seems to have a file reference. You may not have a standard system in your organisation, but if you do make it easy for people to use it consistently in your applications. If you have implemented an electronic document and records management system, then this will make it easier to share information between it and your applications.

Sometimes performance reasons may drive you to implement some of these objects as tables or materialised views. The important thing to remember is that ownership of the data in these tables resides elsewhere, so resist the temptation to create foreign keys between your applications tables and these. By all means create an index, but how can your room booking application, for example, dictate whether an office is sold or destroyed by fire? It can't, of course. You need to find a way to deal with a bunch of bookings for something that isn't there any more. I'm sure you'll manage.