jump to navigation

Friday Philosophy – What ever happened to System Design? September 14, 2009

Posted by mwidlake in Architecture, performance.
Tags: ,

{OK, so it is not Friday, I’ve got a lot on, OK?! Shesh…}

I’m a Contractor/Consultant. As a Contractor I am expected to arrive On Site and within a week be making progress.  As a Consultant the week becomes a day.

That’s fine, it’s the territory. However, there is a question I always ask and I know other people ask it when they arrive on site:-

“Can I please have a look at the database design”.

We normally mean an Entertity Relationship diagram or at least a schema design by this. The usual answer these days?

” We keep meaning to do one, but it never seems to quite get done – here are some misleading and poor notes on a few of the the schemas, if you get an ER diagram together, we would love to see it”.

Why does no one do an ER diagram these days and why is there never a picture of the tables and relationships that you can look at for a system? Despite the fact that anyone new to the system hopes one will exist? What Happened to the skill of logical design?!

When did the ethos of having an overall schema layout just go out of the window??? And how in hell do You (yes, You, the system owner) expect to have a clue about the overall system without one?

Oh, and what is that I hear? You want me to improve the performance of your system without having a description of said system!? You have no clue of the overall database design but you want it to run faster?!? I better set the hidden “_RUN_DATABASE_FASTER” parameter to TRUE immediately then.

Is this just me or WHAT?



1. Graham Oakes - September 14, 2009

Hi Martin, it’s definitely not just you. I still find myself drawing mini ER diagrams for parts of new systems I have to support because no one seems to have one available. My question to this is always – “How did this system ever get built?”.

I couldn’t imagine building one without taking the time to create an ER diagram first, but somehow they’re not deemed necessary anymore. My personal favourite reason for not having one is “Well if we built one it would be out of date as soon as we had a release”….at that point I gave up arguing as I was obviously on a different planet to my co-worker.

btw, if you ever find that _RUN_DATABASE_FASTER parameter let me know where it is!

2. Nigel Thomas - September 14, 2009

Yes, I agree too. I am aware of a project with a seven digit price tag where there is no agreed ERD, and where (not entirely coincidentally) the physical schema includes many tables with no unique or primary keys. And this being built by a team from the company that brought you O****e Designer (then known as SDD or Oracle CASE) way back in the late 80s.

Depressing – we’re moving backwards.

3. Graham Oakes - September 14, 2009

Maybe we can take a leaf out of the BAARF party’s book and create the BANERD party? A stand for common sense must be made!!

4. Nigel Thomas - September 14, 2009

Not sure I want to “be a nerd” if you don’t mind! How about FLEM – Fight for Logical Entity Models? … or maybe not.

5. mwidlake - September 14, 2009

Seven digit price tag Nigel? No model? Can you let me know the planned go-live date and I’ll work out it’s probable go-live date…

I loved CASE and Designer, you knew that by the time you started developing screens you had probably thought about and solved 80% of your data issues. How many of us use little ERDs to discuss development work with out co-workers, whilst bemoaning the loss of the model.

As for BANERD or FLEM, I reckon we could work out some sort of Action group. Justice for Enterty DIagrams (JEDI)? or Designers Insisting on Models (DIM)…

6. PdV - September 15, 2009

Unprepared, but I’ll chip in my 2 eurocents.

There is material for a whole series of blogs on system design and even the “meaning of a system”. A system without some concept and some design is doomed to wander around aimlessly in cyber-space (and will provide us with many days of troubleshooting fees… )

At conception time, any project should have some form of Logical ERD, and a minimum of a Process-Flow-Diagram (Yes, I remember the Oracle*Case tool, the idea wasnt half bad, shame it got lost in migration).

The logical model should at least contain a few items that “users” or customers can relate to (Orders, Customers, Patients, Subjects, Targets, Trucks, Containers, Carriers, CDRs) and the Physical model should bear some (known, traceable) relation to the logical model.

The “Entities” in the system should also have a “life cycle” defined. This used to be known as CRUD: Create, Read, Update and Delete. With modern ETL and datawarehousing, you can replace CRUD by CRUEL: CRU + Extract and Load.
But some knowledge of CRUD is required to know where data gets used. E.g. a Subject or Target can be Identified, analyzed, destroyed and can then be confidentially archived to the DataGraveyard (or field of honour).
The queries on a target must thus be related to any of those activities.

If I get called in to troubleshoot, I do tend to ask for Logicial and Physical models (provided some ppl are still able to listen to irrelevant questions…), and ask about the lifecycle of an entity/record: who inserts it? who reads it? who deletes it? Sit everyone down with coffee/tea/Irn-Bru (good remedy towards shellshock) and play dumb: Explain it to me, please. Simplified pleease.

If it is an SQL-based problem, then like you guys, I will often end up drawing my own little sub-model(s), based on the actual tables and SQL (traces) that I find. You often cannot tweak SQL-based problems without some model (understanding) first.

I am also just waiting for the next generation ESB/SOA/BEPL systems to hit the real-world. The concepts behind those “technologies” will bring us a whole new level of obfuscation and challenges.
Data may no longer live in tables, but end up in “messages”, mostly in transit.

btw: Should we re-recommend ACID-ity, Chris Date and the 12 rules again ?

Long ramble. I’m turning into a Luddite ??

mwidlake - September 15, 2009

If that is you commenting when you are unprepared Piet, I reckon you need to be the person posting about systems design and I’LL comment. 🙂

I used Oracle*Case and Designer/developer when I worked for Oracle back in the mid-90’s. It was great, it gave you so many things:-
– You had to think about the data, how it was structured, how it was used, it’s life cycle.
– Tools helped you gather CRUD data, expected volumes, etc and it produced the DDL scripts (both initial and incremental).
– You could discuss around the ERDs and use them for years after.
– Use of domains and in-built standards gave you a controlled, logically consistent design.
– Logical to physical mappings and implementation. When did I last talk to a designer under 40 who understood that logical ERD and physical Table Design were not the same???! (hang on Piet, how old are you?)
– Prompting and gathering of table and column comments
– Development of the on-line help system via the documentation of the system.
– Generation of screens direct from the design and in such a way you could tweak them easily afterwards.
Ohhh, they were the days.

And when you tell them today? They just talk Ruby-on-Rails to you. Tschhh

7. DBMS SIG « Martin Widlake's Yet Another Oracle Blog - March 11, 2010

[…] not need vast knowledge. It needs a clear, simple approach to solving the problem. And an ERD. I love ERDs and rue their general demise. I use exactly the same method myself to investigate complex SQL performance issues {I think I […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: