Database Design Strategies
Oct 27 2002, 10:39 AM
UA Forum + Wiki Administrator
Joined: 26-September 02
From: Sudbury, Ontario, Canada
I originally posted the bulk of this article in the database design forum. One of the members who read it suggested that I submit it to Gord for consideration to be posted in the FAQ forum. The article begins below:
ome Notes on Database Development
Having developed and implemented several databases, I have reached several conclusions about the process. I'm interested in learning about/from other developers and their personal strategies for database development (whether Access specific or database development generally.)
In no particular order (I consider these points to be equally significant to the over all success of a database development project) these notes discuss the following points:
2. Setting Properties sooner rather than later.
3. Including descriptions for every field.
4. Naming Controls before creating related macros or vba code.
5. Using a naming convention.
6. Following good design principles even for projects that at first may seem to be trivial.
7. Developing written and agreed-to project specifications.
8. Normalizing tables.
9. Designing a friendly and consistent user interface.
1. Extensive planning is absolutely crucial to project success. Time spent in planning the database before ever jumping into implementation is an invaluable investment. It's a bit of a paradox but the more time you spend planning a database, the less time it will take to fully develop the database. There's an old rule of thumb around called the 80-20 rule. Applied to database development this rule would suggest that you spend something like 80 % of overall development time on planning and 20% on development.
2. Setting relevant properties earlier rather than later pays significant dividends. For example, if I plan to get values for a field by looking them up, I should create the lookup source when I create the table. This implies that, if I will be using a table to store the lookup values for a field in another table, I will create the lookup table first, then the table that will be using it for lookup. How do I know I will need such a table? Remember step 1 - planning?
In addition to field lookup properties, I will set field size and caption properties for each field when I create the table. Because objects such as controls can inherit their properties from the object to which they relate, setting the caption property in the field definition allows the label for a control bound to the field to use the field’s caption as its caption. This means you will have consistent labels for such fields on whatever forms and reports (for example) relate to them.
3. Complete the description for every field. Adding a thoughtful description to each of a table's fields will provide helpful status bar text when the user focuses on the field. Once again, this property is passed through objects that refer to the field (such as queries) to other objects (forms/reports, etc.) so the description text will appear on the status bar when the related form control has the focus.
4. Name controls before creating macro or vba code for the control. This will make it much easier to understand your code and to find the relevant subroutine when needed. For example, is it easier to know to what the subroutine relates if it has the name Label21_click or a name like lblSurname_click?
5. Use a naming convention to develop consistent naming patterns for all objects. You can find an excellent and extensive treatment of naming conventions a standard set of naming rules at http://www.xoc.net/standards/. The same site has access-specific examples at http://www.xoc.net/standards/rvbanc.asp#Access .Using a naming convention means that you apply a little more effort upfront in the development process but this small investment will pay big dividends as your project grows and as you need to return to it in the future for maintenance, modification, and further development.
6. Apply solid design principles to every database, even if the database appears to be trivial at the moment. Once again a small investment in advanced planning will pay big dividends when you decide to do more with what started out to be a trivial project. And if that never happens, the time spent applying solid design principles is not wasted. If nothing else, it has kept your development skills sharp.
After I posted this initial ‘seed,’ a number of members responded with additional points just as vital to a successful database project as the initial five I raised:
7. Develop, work from and stick to written specifications. A happy customer is a customer whose expectations have been met. Written specifications serve several purposes at the same time. First, they clarify what the database is and is not supposed to do and for whom it is to be done. Second, they verify that you and your customer (whether internal or external) are on the same wavelength. Third, they bring to light customer expectations that might otherwise be left unsaid. It is much easier to negotiate with the customer up front over an unrealistic expectation than it is to explain it away later to a now dissatisfied customer. Fourth, written specifications provide a benchmark against which to measure the degree of project completion as you proceed through development.
Of course written specifications can be modified; however, the modification should be as thoroughly documented and agreed to as the original specification.
8. Normalization. Normalize, normalize, normalize!!! Access is a relational database. This means your data can be divided along logical lines into several tables. Normalization essentially aims at reducing/eliminating redundancy and duplication of effort. Normalization is an extensive topic all on its own. If you are not sure how to normalize your database, do a search on the internet for relevant articles.
9. User Interface. A database can have the best and most efficient structure possible. It may be built around absolutely exquisite and elegant code. Yet if it has a “Kludgy” and difficult user interface, the end user will be unimpressed with all the behind-the-scenes niceties. I look for interface ideas in every application (database or otherwise) that I use, not to copy them outright, but to get a feel for what works and what does not work. There is a danger of being too subjective in this approach and missing what others may truly be looking for and appreciate in an interface but I don’t stop with just my own opinion. Often I will ask other people to take a look at interfaces that I might believe to be very good or very bad and ask their opinions on what they like and dislike about the interface. (On a personal note, for example, I wonder if we would have the annoying Office Assistant, for example, if Microsoft had consulted potential users before implementing the feature. The majority of people I have asked simply find the Assistant annoying. Only about one person in twenty whom I have surveyed actually likes the feature. Fortunately, it is fairly easy to turn off, at least with Office 2000 and later.)
|Search Top Lo-Fi||23rd January 2017 - 04:20 PM|