Relational theory vs linked-table theory

[Shinken]

I was asked to take over support for an Access database that the client has been using for several years. Perhaps I’m naïve, but I’ve never seen anything like this mess – but then again perhaps relational theory has been supplanted by linked-table theory (lol) and I just missed the boat. Here’s what I’ve found.

1. There are 4 distinct Access databases. When the user clicks the icon for the database, a data entry form is brought up. The form has also has buttons for some reports. However, the data is interconnected and tables in the databases are linked to each other.

2. The tables all appear to be flat files and none are related or have even primary keys, although some fields are indexed. Instead of relating the tables, the "developer" appears to have linked tables from (subsidiary?) databases.

3. One large table has an autonumbered ID column. At first I thought the ID column was the primary key, until I looked at the table in design mode and found that it was not only not a key, but that the duplicates were allowed.

4. The tables & forms have no rules other than data type. Looking at one large table with over 18,000 rows, there are several thousand duplicates, apparently because there were no controls to check for duplicate phone numbers, company names, etc. There were also a thousand or so entries with only a telephone number, but no data in any of the other columns in the row - a useless record.

In an ideal world, I would move all of the tables, forms, etc, into a single database, unlink the tables, set primary keys, relate the tables, and set up a main menu. However, even that will probably present problems, as the developer wrote entirely uncommented VBA routines which will undoubtedly present problems and have to be amended. The cost would probably be prohibitive.

However, the databases have worked for the client, without any significant problems, except for thousands of (almost) duplicate rows and rows empty of usable data, which means that it ain't broke...but would really benefit from some added data entry rules.

I think that the following may be within their budget: Unlink the tables, set some basic rules on some of the columns, such as No Duplicates, set a primary key on each table, and then relink the tables. Unfortunately, I have no experience with such extensive linking of tables among multiple databases and there may be unwanted consequences.

Does anyone have experience with linked Access tables spread out among several Access databases, and can advise me on this? My gut feeling is to just stay away from this mess, but the client is a small business and I’d like to help them out.

Thanks,

Phil

 

[fneily]

Do the 4 separate databases really track unrelated data between the database? It sounds like they could be consolidated. They made have been made at different time periods instead of expanding the original.
You mentioned "flat files". It sounds like they copied Excel spreadsheets into Access tables. I see this all the time in contract work with Fortune 500 companies and it's wrong. After transfering the data, then Normalize. One spreadsheet may create two or more Access tables.
You state the current way has worked without any significant problems. I venture to say certain analysis can't be done because it hasn't been asked for or is avoided.
You talk about cost. That's between the contractor and company. I created the largest Access property tracking database that Maryland Department of Transportation has for a small amount of money. And had a web interface to it. But that's how I am.
Also, because of its' disorganization, it's costing the company quite a bit of money. If it was created correctly, the cost of input of data, maintenace of the database, etc. would drop dramatically and more importantly, statistical analysis for the business could result in profitable business decisions.
How altruistic are you?

 

[Pampers]

If I look at it from an economical perspective, I would say: The database worked for the customer, he is happy...he doesn't care that behind the screen it is a mess. You start modifying it, you will run into bugs and errors and so on. Customers gets pissed off, thinks the old guy was a genius, and you are an ameteur - every dollar extra spent on you must be a waist of money.

Conclusion, don't touch it untill the app stops or customer complaints. You will explain why it doesn't work anymore, old guy will be marked as amateur (which he obviously was) new guy gets credit, client will feel he is spending his money on a good cause.... Happy ending.

Pampers
Just let it go...

 

[Lilliabeth]

Oh my that is the by far biggest mess I have ever heard of.

I don't want to sound coarse, but do be careful that your name doesn't end up attached to that train wreck.

If I were you, I would follow my gut.

 

[Shinken]

fneily & pampers,

Thanks for the replies. I think pampers has a good handle on the situation.

However, fneily, your ideas about actual cost is right on the mark, and I've already touched on the subject of productivity.

When I said that the DBMS (sorry, I cut my teeth on dBase II and rBase, and tend to use obsolete terms) wasn't broke, I meant that the app isn't crashing when they use it, and have lived with the problems for so long that they don't feel a major overhaul is worth the cost. They could have done so much more with this tool, had it been well constructed, but their horizons are modest because they don't really expect much from it other than what it is now. Also, they may not need anything more than a glorified contacts list, because of the type of work they do---hmmm (a light bulb goes on here) this just made me wonder if importing the data into ACT might do the trick for them. Need to check out their paradigm/objectives/etc.

Regards,

Phil

 

[Golom]

The most notable "missing" from your description is what the customer actually wants you to do. "Take over support" could mean almost anything from doing the occasional backup and/or compress to completely restructuring the system. If the customer isn't having problems with the system and doesn't require added or modified functionality then it ain't broke ... despite the apparent chaos under the covers. If they do need changes then you need to be really up on estimating work requirements and change impacts. If you go into this with a "complete restructure" mind-set then you will almost certainly impact every part of the system and generate an unending series of required modifications. Customers just don't understand when their "simple" request to add another field or create one more report suddenly disables some apparently unrelated part of the system.

My experience is that you can evolve sloppy or inefficient coding into a good (or at least better) coding but you cannot evolve a fundamentally flawed initial design into a good one. You must redesign.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Shinken]

golom,

I entirely agree, and have explained this to them. At this point, they have asked me to look at it in more detail and give them an opinion about what to do.

By "take over support" they mean mostly helping them work with the data. For example, one of the databases contains a copy of the main table, and is given to a transcriber to enter more contacts into (from her home), and every month or so, the new contacts are appended to the table in the main database. In the maintime, they are entering data directly into the database. With no error checking except for a single (indexed) phone number column, and no primary keys, you can imagine the disarray of the data in the main table after an append.

However, as I noted in my previous reply in this forum, I've been giving thought about what they're actually doing with the database. Mostly, they're using it as a contact manager, doing email merges from the database, etc. Next time I meet with the client I'll explore this in more detail because I think that I can rename some of the general purpose fields in ACT and can probably map the Access columns into ACT fields and transfer the data. It helps that I noticed an ACT box on their shelf, although ACT isn't installed on their computer. It all will depend upon whether or not they do anything with the DBMS that can't be done w/ACT.

Phil