Hideous Database

[jlt199]

Hey,

I'm VERY new to Access, I had never used it until I started my new job four weeks ago. I have inherited a "Database", it's essentially 94 fields in a single Table and about 4000 records. There are blank, invalid and incorrect entries and I'm tasked with doing something about it. The question is what??

Is it possible to restructure a database with existing data? Should I go the full hog, or just do the smallest changes to reflect the biggest bang for the buck. For example pull out all patients and put them in a separate Table so that we can at least get to the point were there aren't three different spellings for the same patient?

Do I give up on re-structuring and just use VBA to query and filter what's there? The manager of the project has some pretty specific questions they would like to be answered. They are also still collecting data for this project for the foreseeable future.



Many thanks

[Xipooo]

I suggest you investigate "Normalization".

Essentially it's the buzz word used to describe organizing your data into different parts that fit together. It's based on three types of relationships between tables, 0/1-1, 1-many, many-many. There are lots of videos out there as well as articles on the subject.

http://www.youtube.com/results?searc...alization&sm=3

[orange]

I agree with Normalization. You didn't mention your experience/expertise with database, so I'm going to point you to
a) a tutorial that may help, and
b) some videos on database design etc.

Good luck.

[jlt199]

Thank you for your responses. I do know something about entity relationships, having designed another database (my first). My question isn't really related to how best to design the database, but rather can/should I? My questions really are:

1) Can an existing database with 4000+ records be restructured pretty easily? Or does reorganizing the data become too big of a headache?
2) Once the database has been in use for so long is it easier to just make do with what's there?

The database also has a pretty complicated form, for data entry and manipulation, that if at all possible I would prefer not to recreate. Am I asking for the Moon?

[orange]

Here's a few things to consider:

An existing database can be restructured. How easily is dependent on a variety of factors: Is it bogging down business/affecting bottom line? Is it a sore point with the users? Is it an item that management is aware of/should be aware of?
If you take it upon yourself to fix/correct/improve -all by yourself-- you are pinning a target on yourself. You may consider asking users, management etc what is good/bad/needs changing etc in a very positive manner (under the guise of getting a handle on how well the current application is serving everyone).
It is easier to get support if the current system is adversely affecting someone or something (invoicing, inventory, customer satisfaction.. but then to suggest a plan of action to improve things. That plan should help get the necessary resources (people/money/time) to make a better design, test it, involve users, management walkthroughs etc.

But I would NOT undertake independent, non requested action that could bring business to a halt or similar.

If they hired you, and this is your job, then map out a corrective approach and definitely involve others. Management at any level doesn't like surprises.

Good luck.

[Xipooo]

I have the perspective of "the sooner the better".

If you allow the database to continue down this path then before you know it you'll have 20,000 records and a back end that sputters to return data in a timely manner. You'll also realize there are more features or data you want to add and you can't do it the way you want because of the way it's currently structured.

It's not entirely "easy" to move into a more normalized scenario. Usually you have to do it in chunks where you redesign the forms/reports/queries/tables somewhat independently from how the system currently operates, then when it's fully tested switch the old items with the new ones.

We regularly design VBA code and queries to help us copy the data form old sources into our new structures. We can repeat these processes as often as we need right up until the migration is ready. It is more work than if the system was done right in the first place, but not doing it means harder work down the road.

[trevor40]

Your boss may like it if you tell him you will sort out the original in a quick way and also start work on a better one!

[jlt199]

Thanks for your advice, I guess I will start on a redesign then. Or at least some patches to prevent things like names to be entered with several different spellings and for people to be given different genders in different records.

I have another question. The current "database" has over 90 fields. how do you go about building an entity relation diagram with so many fields? I'd need a might big piece of paper! Do you guys use any software, such as Visio, to help you?

[orange]

Work through this tutorial before you change anything. If you have questions post back.

What do you mean the database has over 90 fields? How many tables?

When you finish the tutorial, watch these as you get the time.
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

[jlt199]

It currently has over 90 columns, 4000 records and one table. Hence the use of "database" above.

I will start making my way through the material you have kindly linked to

[orange]

Good luck.

The tutorial should help. For your own benefit and subsequent documentation -write a high level paragraph about WHAT the database is about. It should be understandable by you, management and the guy on the street. It will help with focus.