The Scene: A meeting room in the IT department of a large company in London. The meeting is in its third hour and the participants lie slumped in their chairs, like discarded puppets. The walls are covered with crumpled ER diagrams full of boxes and crow's-foot arrows. An air of doom permeates.
Phil Factor
(fending off black despair) Right. It looks as if we can't agree on the data architecture for the trickier parts of the new application, such as the back office functionality. Let's take a step back and do something easy. What about looking at the NAD side of the application (Ed: NAD=Name and Address). This'll be nice and simple, hopefully. (Thinks: if I can catch the early train, there's that nice pub in Aldeburgh….)
(Developers look up from their notebooks and stare bleakly at Phil)
Phil
(with strained enthusiasm) Well, how about a nice simple table with the customer's name, phone number and contact address in it?
Dan the Data Man:
Wait a second, I can see problems already. People can run accounts under different names. Single customers will have many names.
Developer:
Surely, we can just tell them they can't have more than one name?
Phil Factor:
(Thinks: ...and people wonder why we are reluctant to let the developers out of their cubicles)
Dan the Data Man:
(patiently) It is perfectly reasonable for our customers to have accounts under several different names. Disallowing this might even be seen as discriminatory but in any event it would certainly be inefficient. If we treated them all as different customers we'd end up phoning or visiting them several times in a row. It happens occasionally and we know that our customers don't like it. And besides, this is real life – a married lady can use her own surname or her husbands, and even call herself something like 'Mrs Phil Factor'.
Developer:
(attempting humour) But that could also mean that Phil has had a sex change?
Phil Factor:
I say, dash it chaps!
Dan the Data Man:
Ah, quite. You've identified another issue. Oh dear, the complications whenever that happens. Sex changes are date-related so that if we print out transactions before the sex change they'd have to be Mr Phil Factor, and after the date they'd be Ms Philippa Factor.
Developer:
OK. So we'll have to put the names in a different table and have a many-to-many relationship with the Customer table
Alan Analyst:
The application developers will never understand the SQL for the CRUD (Ed: Create, Read, Update and Delete). Their embedded code will cause havoc and the DBAs will demand an interface based on stored procedures.
Phil:
(to himself) Yes!!! Result!!!
Dan the Data Man:
You want to store contact addresses too? (Sucks through teeth and shakes his head sorrowfully).We'd need to record all of the addresses associated with each customer. What about billing addresses, Bankcard addresses, work addresses and so on?
Dan the Data Man:
Are we recording the customers' contact numbers as well?
Phil Factor:
Of course. Sales have put in the requirement and it would seem a good idea to recognise the CLI (Ed: Caller's phone number) when a customer phones into the telephone banking system….
Dan the Data Man:
A customer will have a home phone-number, a work number, a mobile number and so on, some of which will be current and others which will have expired at a certain date. This means that we'll have to record the type and termination date of each entry.
Phil Factor:
(with a pathetic attempt at bringing the meeting to a close before his train goes) Well, is there any way we can let the code-jockeys create their own Customer object and just 'persist it' (Ed: store it) as XML in the database?
(Various developers bob up and down in their seats with excitement)
Dan the Data Man:
The last time we did that, it was pandemonium. We ended up with six mutually exclusive definitions of what a customer was and none of them recognisable by the business.
Derek DBA:
(between clenched teeth) …and no ordinary mortal could maintain the production system when it finally emerged.
(Developers relapse into surly quiescence)
Developer:
Yes…so we'll have to put the contact numbers in a different table and have a many-to-many relationship with the Customer table The linking table will need the date stamp and the type of contact number (thinks: is this Groundhog Day?)
Alan Analyst:
Well, also, marketing want a whole lot of other customer details stored, so they can work out the demographic profile of purchasers. They're dead keen but rather unclear as to what these details will be.
All:
'One True Lookup Table'!
Phil Factor:
OK! OK! So someone think of a better way of making changes to an entity on a production server without needing to do any change management procedures? We're going to need an EAV (Ed: Entity Attribute Value) table just to bridge the gap between marketing and reality, by allowing 'soft attributes'.
Dan the Data Man:
We generally find that marketing have very short memories anyway, so we can time-bomb the additional 'soft' attributes. We call it the 'Goldfish' mechanism..
Phil Factor:
Excellent! We seem to have a broad agreement on the general principles of the design. There's no sense in getting into to much detail at this stage. (Thinks, if I run part of the way to Liverpool Street station, I'll get that train)
Colin Compliance:
(clears throat) I'm a bit concerned about the auditability of this NAD.
Phil Factor:
(Thinks: Two million years of human evolution just to get a Compliance officer! Nature can be cruel. It doesn't seem right)
Colin Compliance:
This design isn't going to get sign-off by the audit-compliance team. Do you realise that we have to be able to reproduce invoices, advice notes, returns and such paperwork exactly as first done? We have to audit when an address changes. Any change of name has to be logged with date, time, the database user and workstation. There have been many frauds in the past that have involved changing a customer's address, so the audit-compliance team will have a feeding-frenzy on the design as it is so far.
Phil Factor:
(thinks: OK, next train in an hour's time. Might as well spread the anguish) Good thinking, Colin. So that means we'll definitely need to have all CRUD done via stored procedures, with a separate log for all changes.
(Developers slump, despondent)
Colin Compliance:
(warming to the subject) Yes, the design will have to have a full audit trail. We will need to make all tables time-based so that all versions are kept, with an insertion-Date and termination-date, and the SQL picks out the current one only due to its null termination-date. We'll need a trigger to enforce the rule of having only one record with a null termination-date where one would otherwise have a unique constraint. We can then cross-check it with the separate audit log.
Phil Factor:
(recovering from a near-death experience) Quite so, some interesting ideas there.
Derek DBA:
Someone is going to ask for an independent Data Protection audit report on how we conform to the Data Protection Act. Marketing always sail close to the wind. Maybe we'll need to encrypt some fields, over and above the usual security precautions.
Dan the Data Man:
Security issues aside, we'll probably need to allow customers to check all data we hold on them for accuracy. Has anyone studied the Freedom of information act and the Human Rights Act for compliance issues?
Colin Compliance:
Yes, and we haven't even started discussing Sarbanes-Oxley, FSA, HIPAA, HSPD-12, BASEL II, SEC, FSA, FASB (Financial Accounting Standards Board) and Gramm-Leach-Bliley…
Phil Factor:
(Thinks: I have an hour to kill, a concealed iPod and a thirst for revenge) Yes, I think we should bottom out all these fascinating compliance issues first and then move on to identify any potential performance concerns. But regrettably we must be brief, as the room is only booked for the next fifty minutes.