Documenting a Complex DB for the Future

[Guest]

I'm developing my first "pretty complex" and "big" database and associated
systems in Access, and expect to integrate the system with other MS Office
functionality. I've kept "Development Notes" as I've been going along, but
would really appreciate opinions and examples from "youse experts" about how
to document a system to support future maintenance and further development.

The "Documenter", "Relationships" and other facilities in Access are, of
course, useful. But they don't EXPLAIN WHY you choose to do certain things a
certain way, etc.

I'm expecting to hire people in the future to take over maintenance and
further development of parts of the system. I expect to "sell" some or all of
the system to another company at some point. At that time a well-documented
system would make the system much more valuable to a potential buyer.

Can you suggest links to good examples of how to do this type of
documentation? Any other comments are appreciated.

Thanks - John D

 

[Guest]

Hi John,

There are a number of tools available to assist with documenting your
database, but the best piece of advice I can give you is to comment your code
- it doesn't matter how "obvious" you think something is, put a comment to
explain what it's doing.

As complexity increases, so does risk, so the more obscure the coding, the
more commenting there should be.

Also, if you haven't already, you should consider baselining your product
(once it's ready for release), and commencing some sort of Change Request
register to record changes you are making. This is the type of thing
companies would expect if they are going to purchase some software off you
(the code itself, not a working product).

Hope this helps.

Damian.

 

[Albert D. Kallal]

Good for you!!

The "Documenter", "Relationships" and other facilities in Access are, of
course, useful. But they don't EXPLAIN WHY you choose to do certain things
a
certain way, etc.

#1 on the list is a good table diagram.....

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added).

In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.