Systemic Database Design Issues

[Thales750]

2014 marks my 20th anniversary of relational database design experience.

Starting with Paradox in '94 and switching to Access after the release of Windows 95.

Well now, 20 years later, I thought I would start a thread of the most common problems with database design. Maybe we can help the next generation of developers avoid some of the pit falls we encountered.
I think the biggest problem I continue to see is the wrong information being entered into the wrong fields. It seems every time I inherit an existing system I find thousand or even millions of records with completely erroneous data in them.

This, and other consideration, leads me to the conclusion that all data must be entered through a query of some kind or another. And that in many cases, some form of wizard must be used to modify, or enter data.

So I will say, lack of data entry control is the biggest problem.

What do you think?

 

[jdraw]

I think you're hitting symptom. I think the big issue that is repeated again and again is jumping into the dbms specifics (dbase, access, oracle, MySql..... whatever) without a clear understanding of the business/business rules. This could be considered insufficient analysis and/or design. There certainly are a lot of posts regarding Access/vba syntax, but many posts don't have any business detail to put a context on issues. Another issue that is near the top is a lack of understanding of Normalization.

 

[ButtonMoon]

The biggest general problem is the one you are describing as "data entry control" - which fundamentally means inadequate implementation of business rules. More specifically: lack of data integrity constraints; weak typing; use of nulls. Such problems are a legacy of SQL and its implementations which generally speaking don't provide good support for data integrity and rich typing.

 

[Thales750]

I guess debating is inevitable, on a forum. But I see business rules as big picture plans and a group of processes for solving a what, not a how.

I see controlling data entry as a systematic, or a tactical application.

I think as developer, we are responsible for having modular designs that can be mixed and matched to create systems that conform to the business parameters.

So that no matter what the "business requirements" are, our systems will control data, have inherent properties to normalize, and are reusable.

So to that end I once again go back to wizards. Wizards don't have to be multipage next, next, next, finish types. But they should only allow certain type of data to be entered into the system.

Next you must have some fields that are not highly restricted for unstructured data. So notes can be attached to records.

 

[ButtonMoon]

I see business rules as big picture plans and a group of processes for solving a what, not a how.

Absolutely!

 

[The_Doc_Man]

I have always espoused what I refer to as the "old programmer's rules."

If you can't do it on paper, you can't do it in Access... meaning that first comes enough understanding of the business data flow - THEN comes automation and Access implementation. But if you can't sketch it out on paper first, you don't know what you are doing (yet). Not that you actually have to write it out on paper, but if you can't do so, you are not ready to do squat.

The other "old programmer's rule" I often use is "Access won't tell you anything you didn't tell it first." Since the headline topic represents issues in design, it is relevant in the sense that if you are going to want to ask a question, you should design the database to be able to capture or accept the data you will need to display or compute your desired results.

You could summarize the two rules rather simply by saying: Know from where you are coming. Know where you are going. These things have NOTHING to do with the language in question or platform. They are design issues, not implementation issues.

As a side note, I first did database work maybe 30 years ago with a product called Datatrieve (made by Digital Equipment Corporation). Not TOO long after that, I got into Paradox for DOS. Loved it. Then got involved with Paradox for Windows. Hated it, but Microsoft had a special offer to get you to switch to Access if you had any other vendor's product on your system. Paradox was one of the targeted vendors, so I bought the cross-over version of Access 2.0 which ran under Windows 3.1 - and yes, I've been in the industry that long.

Even back then, Access had Rapid Appication Development (RAD) capabilities that I used to make a data entry form when I sold my old comic-book collection. (It wasn't good enough to be a great collection - most of the books sold for pennies but some were worth more than cover value.) Ever since then, I've had Access on one or the other of the computers I've used over the years.

Back to the topic, I recall that most basic rule that doesn't apply specifically to computer work, but rather applies to almost anything you can imagine in the world. I ask forgiveness for a minor vulgarity, but it was taught this way...

Piss-Poor Project Planning Provides Pathetically Poor Performance

The reason most people have trouble with an Access or any other large project? They shoot from the hip and don't spend time preparing or planning for what they intend to do. So what are the odds of Joe Average Programmer getting it right while shooting from the hip? I don't know, but I'd hate to have to live on commission if that's the way I did things.

 

[Lightwave]

If you can't do it on paper, you can't do it in Access... meaning that first comes enough understanding of the business data flow - THEN comes automation and Access implementation. But if you can't sketch it out on paper first, you don't know what you are doing (yet). Not that you actually have to write it out on paper, but if you can't do so, you are not ready to do squat.

I've come across project managers who neither know the technology or know the process they are trying to implement.

You know those projects are going to be a struggle.

There are definitely clients that struggle to outline their requirements. I am aware that I sometimes struggle to pick up on the significance of certain aspects of their work. I must come across as remedial when I am scoping projects sometimes.

I often ask questions like

Can I just watch you enter your information?
Can I maybe ask some questions of your staff?

You can get dramatically different opinions from staff as compared to managers and there is quite often a individual staff member that is more switched on than their manager. I usually try and keep in contact with that invidividual as they are totally invaluable for quick feedback and constant testing.

 

[jdraw]

I agree Lightwave -always talk to someone who is "doing whatever it is"; be observant and ask questions. The answers are always interesting and valuable. Get the people at various levels involved - make some
" comments you know can't possibly be correct" and let the others/workers point out your "lack of understanding".

 

[The_Doc_Man]

Absolutely concur. The "guy in the trenches" (substitute "gal" as appropriate) knows what they get as input or as questions. The Big Boss rarely has a clue as to details but knows that his people seem to be inefficient at what they do.

One of my personal rules for preparing to make some product is a THOROUGH (to the point of sometimes being a pain in the ... patootie) investigation of the problem. I return to one of my favorite authors, Robert A Heinlein, and his story Stranger in a Strange Land, in which he invents the word "grok" (literally meaning "drink") as a way to understand things. In the context of the story, you must drink in your problem until it is part of you. Then and only then can you correctly address it.

 

[gemma-the-husky]

I disagree with the original premise, I think.

in a CAREFULLY designed system you should not get any erroneous values. The system should be designed in such a way that erroneous values are prohibited - this by a combination of data typing, key design and data entry validation

I think bad data is more often seen in systems managed by spreadsheets - which lack the rigour generally available in Access, or other databases

The bigger the system, the harder it gets, but it should be eminently achievable.