Understanding Existing Databases

[BigJimSlade]

Hi, Big Jim Here:

I was hoping you guys would have a recommendation. Do you know of a book or web site that offers workbook examples to help practice learning how to understand existing databases?

My biggest problem with databases has never been designing them but learning how to follow someone else's work. Some place to practice on this deficiency of mine would be great. Any thoughts?

Thanks in advance,

Big Jim

 

[systemx]

Hi BigJim,

From my experience (although limited) - I have found there is no easy way to get a grip on another person's database. As different people use different conventions and method's - this has posed a problem for me in the past.

I find the best way to understand the database, is by creating a copy, examining relationships, dependancies and literally just exploring/breaking/tinkering.

Regards,

Rob

 

[The_Doc_Man]

Somewhere on my list of things to do - and to NOT do - there is this sequence:

Have a root canal with weak anaesthesia
Spend a month with my mother-in-law
Examine someone else's code
Muck out a stable that hasn't been mucked in a week
Undergo Soviet-style interrogation

Seriously, Rob (SystemX) gave as good an answer as any. Start by getting a complete documentation of everything. The Tools >> Documenter will do this for you. You want every table, every field, every query, every report, every form, every macro, and every module. You want all the relationships.

You also fervently want your predecessor to have been a maniacally fanatic commenter. (I'm just short of the manically fanatic level myself.)

This is a case where my favorite method of DB construction might also apply to DEconstruction. For every table, put something on a dry-erase board. Put a few sticky notes representing records in each table. Draw lines from table to table in order to express relationships. Go through the mechanics of what is done by each query and form. See what each report considers worthy of being shown.

When you get to the modules, be extra attentive to what each entry point does. Do checks on jumps to get a hint of code "locus" - basically, the parts of the code that get run a lot.

But if all of this sounds terribly tedious, you've been listening!!! It IS terribly tedious. Which is why my excerpt above includes it at the low end of my desirable experiences list.

 

[BigJimSlade]

Thanks gentleman,

Unfortunately, I work with many databases which were built by people who never documented. Also, there are no relationships. Access (in this particular project) has been used as essentially a giant report generator. It takes 2 months to run all these steps to come up with a final result. There is no user interraction really, just this huge back end.

I'll definitely practice your ideas, Doc, but I am also curious. Do you know of any books or sites that provide sample databases and give you assignments to see if you can really follow the current design?

Thanks again,

Big Jim

 

[boblarson]

I would say that if they haven't been documented, and there are no relationships, then example databases aren't probably going to help as it is highly likely that the people who built them weren't programmers and probably didn't even follow any standards.

I had the joy of getting 3 large reporting databases that someone else built while they were learning Access. Now, they did a great job of getting the job done so that they could get the data they wanted. However, there were no naming conventions, things like Text45 were names for text boxes on forms and reports (so I couldn't easily tell where things were coming from), they had queries upon queries upon queries to do aggregates but the names of each level of query was in no way associated with the other (nor the table, nor the report). It took me 6 months to fix those 3 databases due to having to "reverse engineer" everything and rename stuff. To top it off, they had set it so that to run a report, the person had to open the query, manually type in the criteria, save it and run the report for one physician, then close it and do the same for all 350 physicians. It took the person 2 weeks every month to run the monthly physician reports. I added a GUI to the process and allowed for batch printing and took the 2 week process down to about 2 minutes.

So, good luck to you. I don't envy your position at all, but it can be done - it just takes a LOT of work.

 

[The_Doc_Man]

BigJim, I am not aware of any books or other references such as you mention.

If you could find the person who wrote this and slip them some Thallium Nitrate in their food, maybe just a couple of micrograms every few days, they would stop doing such abominations. (ThNO3 is a poison, so don't REALLY do it. But enjoy thinking about it.)

 

[BigJimSlade]

Quite the mess, I must agree. I'll take the legal suggestions above to heart.

 

[Anthony George]

Useful Material

Hi

Have a look here Jim

http://www.functionx.com/access/

Regards

Tony