Help explaining why a database is no good to non-database, non-technical people.

[EDNYLaw]

This will be a little lengthy, but I would greatly appreciate any input.



I am an attorney and I have dabbled in programming databases and have successfully (with the help of this forum) launched some fairly technical databases. That being said, I am certainly no DB admin or programmer. Here's the issue: One of our clients is a bank. We draft forms for the bank using an old system cobbled together by a former paralegal that I've reworked. It's basically using Word Documents to merge in variables from a separate Word Doc. I've coded in some if/then statements and some calculations. It works just fine. We have multiple people within our firm who use this system to draft documents. Therefore, multi-user is an absolute must. Moreover, everyone who uses the database needs access to it, e.g., if I do something on my computer, my paralegal needs to be access the database from her computer. So that means that it must be on a network drive and not on individual computers.

The bank now wants to roll out an Access database that someone in house developed. This person is clearly not a developer of databases. I'm not even sure if they've ever used one. The bank designed a database with a single table where ALL data is stored. I've attached a copy of the database where I've cracked their password (a little hex editing) and cleared out the data. They had the the form password protected but not the database itself.

Here's my request: Can people give me some good talking points regarding why this is such a ridiculously horrible database?

Since I'm the only one in my firm (and apparently the bank) that understands why this is terrible, I need to be able to explain this in a way that is fairly easy to understand but still conveys how utterly stupid this database is and the shear incompetence of the person who programmed this.

I would like to talk about the data instability because this will either a) be stored on everyone's individual computers (thus making multi-user impossible); or b) be stored on a shared drive without splitting the database. I would also like to address the data integrity issues with using only a single table to store all data.

Oh, and the kicker? Instead of running a report in Access, this is how we get the data: Export the data to an Excel File then merge the Excel file into a Word doc. If anyone wants to chime in as to the technicalities of why that is stupid as well, feel free. My thought is, when you're exporting from Access, to Excel, then to Word you've at least tripled your potential error rate.

Basically, I need to attack this from a data integrity angle that brings in how this will cost/waste money. Any help on some of these talking points would be greatly appreciated as I can kind of convey why it's terrible, but I'd really like to shore up my points with some technical issues.

Thanks!

Database.zip

[ssanfu]

OK, I'll take a stab at this......

b) be stored on a shared drive without splitting the database.

This will result in data corruption. It is not a matter of IF, just WHEN. Understand, sooner or later, it WILL be corrupted.


For a relational database, designing a table like a spreadsheet is a poor design. There will be a lot of wasted space.


Is a borrower a person or a business? Storing the individual's name in one field violates normalization rules. Plus you could have entries like
John Smith
Smith, John
Smith John
SmithJohn



As is, you can have only 4 borrowers and 6 guarantors. If you have less borrowers/guarantors, you have wasted space. If you need more of either, you have to redesign the whole dB. In a normalized relational dB, there would be no wasted space and you could have as many borrowers/guarantors as you want/need without a redesign.


There are fields in the table that do not seem to be on the form (ie FileNumber).


There are spaces and special characters/punctuation in field names. (very bad to have).


Unless you changed object names, the export function won't work because there is no table/query named "ESLFieldsQuery".

[EDNYLaw]

OK, I'll take a stab at this......

This will result in data corruption. It is not a matter of IF, just WHEN. Understand, sooner or later, it WILL be corrupted.


For a relational database, designing a table like a spreadsheet is a poor design. There will be a lot of wasted space.


Is a borrower a person or a business? Storing the individual's name in one field violates normalization rules. Plus you could have entries like
John Smith
Smith, John
Smith John
SmithJohn



As is, you can have only 4 borrowers and 6 guarantors. If you have less borrowers/guarantors, you have wasted space. If you need more of either, you have to redesign the whole dB. In a normalized relational dB, there would be no wasted space and you could have as many borrowers/guarantors as you want/need without a redesign.


There are fields in the table that do not seem to be on the form (ie FileNumber).


There are spaces and special characters/punctuation in field names. (very bad to have).


Unless you changed object names, the export function won't work because there is no table/query named "ESLFieldsQuery".

Thank you very much. I'm glad that made enough sense for a great response, as it's kind of a weird question.

I just changed the form name as I was messing with it and trying to crack the password on the VBA Code on the form.

The corruption issue is definitely something I want to hammer on. That's my biggest concern.

Your other points are well taken and I'm going to definitely address those.

I can't believe this database was put forth seriously. I hope that lay people will be able to understand how bad this is. The problem is, they're totally non-technical and it looks nice (well, nice enough) so it must be good.

Is... is this what it feels like to be IT or an admin?

Thanks again for the response.

Sent from my Pixel XL using Tapatalk

[CJ_London]

Ugh - no wonder banks are so inefficient.

Have they actually provided you with the word document that will use the excel export? And yes, pretty stupid - word could just link to a query in access

Further thoughts to Ssanfu's comments, my twopenneth

Selecting a borrower name - you can only select the first borrower - what if the person you want is 2,3 or 4?

May not be relevant since the facility is not provided, but any way of trying to find if a borrower is already on the system will require searching the same data 4 times.

databases are not spreadsheets

for some reason, the ID field is autonumber, but not indexed. So searches will get slower and slower

Borrower types are hardcoded - not sure they cover all the legal entities that would apply?

Not sure if it is because of what you did to 'break in', but there is no startup form, users have full access to the navigation window so could delete or modify a (sorry the) table or the form or query.

So much for data security - anyone could walk off with a copy of the database and sell it on the street, or just copy and paste the table

[EDNYLaw]

Ugh - no wonder banks are so inefficient.

Have they actually provided you with the word document that will use the excel export? And yes, pretty stupid - word could just link to a query in access

Further thoughts to Ssanfu's comments, my twopenneth

Selecting a borrower name - you can only select the first borrower - what if the person you want is 2,3 or 4?

May not be relevant since the facility is not provided, but any way of trying to find if a borrower is already on the system will require searching the same data 4 times.

databases are not spreadsheets

for some reason, the ID field is autonumber, but not indexed. So searches will get slower and slower

Borrower types are hardcoded - not sure they cover all the legal entities that would apply?

Not sure if it is because of what you did to 'break in', but there is no startup form, users have full access to the navigation window so could delete or modify a (sorry the) table or the form or query.

So much for data security - anyone could walk off with a copy of the database and sell it on the street, or just copy and paste the table

The actual database starts with the form. I disabled that while messing around because I didn't want to keep holding shift on open every time.

Can you explain the autonumber/index thing. That sounds like something I might want to mention (searches getting slower) but I would need to explain, at least generally, why that happens.

Hardcoding entity types isn't too bad, there's only about 5 or 6 types (I'm sure they listed probably 2 or 3, I didn't look). It would be better to just enter it.

Can't Access generate a report in Word format? I think that's a function but I've never used it.

Not worried about the actual security of the database, since it's only used in our firm.

Yes, this is all in the name of "efficiency." Can you feel the eye roll?

As cobbled together as our current system is, I can edit it on the fly, I'm not worried about corrupting an entire database, and I can tweak it as necessary without remembering how to properly code for databases.

Thanks for the comments!

Sent from my Pixel XL using Tapatalk

[John_G]

Ouch!

I think what was done is that the person whe developed (and I use that term loosely!) this has the impression that one output report or paper form had to be represented by one table, and this is the result.

Just to add some addtional thoughts -

In an application such as this, there is no way to distinguish between different people who happen to have the same name. The name should never be depended upon to uniquely identify people. Name + DOB is sometimes good enough, though - but the database doesn't have it.

If a borrower or guarantor moves, a) it won't be easy to make the required changes, and b) how can you be sure all occurrances for that person are changed if you can't be sure the name is always entered exactly the same way and c) with respect to the comment above, how do you know you changed the right one? The last thing a bank wants is for someone's financial information to go to the wrong person.

The table allows for only 4 borrowers - what happens if there is a larger group? And only one borrower attorney? The same question goes for guarantors.

If they wanted to do a bulk mailout to people in the database, it would be next to impossible to avoid duplicates.

Very few of the fields that will contain numeric data are defined as numeric - they are text. How can any kind of proper calculation or querying be done with it?

I use an Access-to-Word process myself, and what I do is to create an MS Word template document, containing bookmarks where I want data from the database to be inserted. MS Access opens a copy of the template, inserts the data as required, then saves the document with the appropriate name. No need for Excel.

You can point out to your client that they didn't create a proper database - they created a "spreadsheet on steroids", which (as has been pointed out) is virtually guaranteed not to work.

Depending on how strong you feel your skill set is, and since you are working with a financial institution, I think you ought to consider hiring a professional to do this, especially if the database will be the system of record.

Good luck with the project - don't hesitate to ask for further input.

[Micron]

How about this for an analogy for non-techie, non database types of people:
Imagine the database was a bus. How would you like to have to take apart the bus to get another person on? That's what you have here.

They might think you're exaggerating when you repeat what ssanfu says about having to take apart the db to add an entity such as a borrower, but he's right. The tragedy will propagate throughout the tables, forms, queries and reports to say the least. There is nothing I can see to comment on other than what these fine people have already said. I just thought you might be able to use something they could relate to.

[CJ_London]

Can you explain the autonumber/index thing.

The ID (which is always a bad name to use, but that's another story) is used to uniquely identify a record. To do so it must be unique - the autonumber datatype enables this by providing a unique value for each new record (the auto part). The number generated means nothing in itself and should not be given any other meaning (such as a contract reference/invoice number etc).

So that is why the developer has used it. However he has not indexed it - so to find a specific unique ID (which is what happens when a user selects a name in the combo box) access (or any database) has to search through the ID's sequentially, one at a time. If the field was indexed the search would be much faster, perhaps in the order of 100 time faster.

It is just as an example of 'why this is such a ridiculously horrible database'.

But my guess is the bank has rolled this out as cheaply as possible to a number of organisations, some of which do not have your skillsets for manipulating word. Within itself, it does the job from the banks perspective, they just haven't given it much thought from yours

[EDNYLaw]

Ouch!

I think what was done is that the person whe developed (and I use that term loosely!) this has the impression that one output report or paper form had to be represented by one table, and this is the result.

Just to add some addtional thoughts -

In an application such as this, there is no way to distinguish between different people who happen to have the same name. The name should never be depended upon to uniquely identify people. Name + DOB is sometimes good enough, though - but the database doesn't have it.

If a borrower or guarantor moves, a) it won't be easy to make the required changes, and b) how can you be sure all occurrances for that person are changed if you can't be sure the name is always entered exactly the same way and c) with respect to the comment above, how do you know you changed the right one? The last thing a bank wants is for someone's financial information to go to the wrong person.

The table allows for only 4 borrowers - what happens if there is a larger group? And only one borrower attorney? The same question goes for guarantors.

If they wanted to do a bulk mailout to people in the database, it would be next to impossible to avoid duplicates.

Very few of the fields that will contain numeric data are defined as numeric - they are text. How can any kind of proper calculation or querying be done with it?

I use an Access-to-Word process myself, and what I do is to create an MS Word template document, containing bookmarks where I want data from the database to be inserted. MS Access opens a copy of the template, inserts the data as required, then saves the document with the appropriate name. No need for Excel.

You can point out to your client that they didn't create a proper database - they created a "spreadsheet on steroids", which (as has been pointed out) is virtually guaranteed not to work.

Depending on how strong you feel your skill set is, and since you are working with a financial institution, I think you ought to consider hiring a professional to do this, especially if the database will be the system of record.

Good luck with the project - don't hesitate to ask for further input.

Thank you for the great feedback. I'm taking a lot of your points and writing them up so when I have a meeting with the partners at my firm, I can explain to them why this is a disaster.

One question that has gone unanswered from above is why does having the ID field autonumbered but not indexed mean searches will get slower? I don't really understand that. Do you think you would be able to explain that (even though you're not the person who brought up the point)? That sounds like something that may be relevant to bring up, but I hesitate to bring it up if I'm not familiar with it.

I think, given enough time I could make something work. Except, I'm an attorney and I live by the billable hour. None of this would be billable. Therefore, I couldn't devote enough time to it. So, I'm totally in support of making the bank hire a professional. This is penny wise and pound foolish. They made a secretary create this which saved them money, but it will cost tons of money because it makes attorneys work longer (more time, more money) and when the data becomes corrupt, we'll have to start from square one.

It's shocking how people get into positions of power where they have no idea what is going on. This is one of the real problems with technology, well where technology is so integrated with everything, people who are "in charge" have no idea what is good and bad and then rely on bad information and it's hard to explain why it's bad because it requires some technological awareness or willingness to learn, neither of which is prevalent.

Thanks for the input. I really appreciate it!

[EDNYLaw]

The ID (which is always a bad name to use, but that's another story) is used to uniquely identify a record. To do so it must be unique - the autonumber datatype enables this by providing a unique value for each new record (the auto part). The number generated means nothing in itself and should not be given any other meaning (such as a contract reference/invoice number etc).

So that is why the developer has used it. However he has not indexed it - so to find a specific unique ID (which is what happens when a user selects a name in the combo box) access (or any database) has to search through the ID's sequentially, one at a time. If the field was indexed the search would be much faster, perhaps in the order of 100 time faster.

It is just as an example of 'why this is such a ridiculously horrible database'.

But my guess is the bank has rolled this out as cheaply as possible to a number of organisations, some of which do not have your skillsets for manipulating word. Within itself, it does the job from the banks perspective, they just haven't given it much thought from yours

Thank you for the explanation. That makes sense (to me). Considering we do several hundred of these per year, it seems like it will slow down fairly rapidly.

[CJ_London]

to understand the need for indexing see post#6 of this thread

https://www.accessforums.net/showthr...d+wise+foolish