Existing Database - Create new or Fix existing?

[Guest]

Hi,
I downloaded the free 'database checker' utility from
http://www.allenbrowne.com/AppIssueChecker.html and used it to analyse our
company database. The results are NOT good (54 page report!)

So...
Do I trawl through the report page by page and try to fix the issues one by
one or should I create a new one from scratch?

I'm thinking of copying the database and then dropping each table into a new
database, fix all the bad naming conventions etc. and then design new
queries, forms and reports later. Any advice would be most welcome.

Thanks.

 

[Allen Browne]

Mike, please use that information intelligently. The fact that the utility
identifies an issue does not mean it is an application killer.

The issue of reserved words as field/table names is a thorny one. You might
get away with it, or you might hit scenarios where the names are
misinterpreted. If you have spent lots of time developing the app, and it
seems to be working fine, you might continue to use the app and just keep in
mind that this could be an issue of a problem crops up.

Most of the other issues can be addressed without the need to redesign the
whole app. For example, if you use spaces in your field names, adding the
square brackets around the names will slow you down during development, but
will not cause your appliction any problems once you have it developed.

Similarly, if you have relationships without refential integrity, you can
fix any bad data that may have crept in, set up the relationships correctly,
and continue using your application.

My suggestion would be that if you are experiencing problems and
inconsistencies and the issues are significant enough for a complete
rebuild, feel free. Otherwise, fix the issues you can (such as ensuring all
tables have a primary key, and foreign keys are not unintentionally
orphaned), and keep the others in mind as you maintain your application.

 

[Guest]

Hi Allen,
I built the app in 1997 and have been adding to it ever since. It's pretty
messed up and not user friendly at all. Dozens of queries, forms and reports
don't work any more due to 'table' changes etc.

It definitely needs a good clean up and a switchboard front end would be
nice too. I need to read up on referential integrity, relationships and
primary/foreign keys again to prevent making the same mistakes again.

I think that starting afresh will allow me to get it right first time rather
than searching around trying to cure problems caused by previous mistakes.
I'll continue to use the 'database checker' as I build and will no doubt be
posting lots of questions on here

Thanks
PS...
What does the 'Subdatasheet' issue mean? Can I fix it or should I leave it
alone?

 

[Allen Browne]

Fair enough, Mike. I suspect that lots of people have an experience like
that. You start on something that's going to be helpful, without much
understanding of data design principles, and then it just continues to grow.
Ultimately, that's one way to learn about how to build a database. It may
not be the most efficient, but it is a very practical way to learn.

So, it makes sense that you will now put that work to good use in building a
good, relational design. Your existing "prototype" has probably given you a
very clear picture of what you need to achieve, so go for it.

The "subdatasheet" issue does not apply to Access 97. In Access 2000 and
later, when you open a table you get a little plus sign to the left of each
record, and clicking the [+] displays related records right there in the
datasheet. Casual users may find this nice, but professional developers
generally don't want to have Access guessing what related data it should
show, and don't want the performance issues that result from Access drawing
not only the table you want but also the related data each time it gets
data - forms, subforms, combos, list boxes, reports, and so on. Expecially
on a split database (front end/back end), this is a serious performance hit.

Tony Toews has more info on this and other performance issues here:
http://www.granite.ab.ca/access/performancefaq.htm