Steps to designing a database (All)

Topic

New Reply

Okay, I’m just trying to get the steps with which other loungers design their databases. I’ve already hand-drawn my required relationships, etc, as well as outlining the tables and forms I will need, as well as some other functionality (i.e. synching deployed front end designs with master front end elsewhere on the server, etc..). I plan on designing my forms (without any info behind them), and then create the queries as needed, and move on to reports, etc… how do you all do it?

Reply | Quote

Replies

Hi Jeremy,

That’s a rather broad question!

What aspects of designing forms, queries and reports would you like us to comment on?

Reply | Quote

Hans,
I just mean in general, some simple steps you follow, sort of a personalized “logical progression”…. just wondering what works for other people. Since there’s “doctrine” out there from so many books and I’m positive not everyone follows that, I’m just looking for ideas before trying to make my first “production” database (that is, one that will be used by me, and passed on to the unit that replaces mine, import their info, etc). I have a lot to incorporate into this, and I’m just wondering how other loungers think.

Reply | Quote

I’ll start by designing the tables and queries I need.

I often use a Switchboard form as the central form of a database. I use a slightly modified version of the one created by the Switchboard manager, I just copy it from a master database into each new database.

I often use the Form Wizard or an AutoForm/AutoReport to create the first version of a form or report, then modify it extensively to meet my needs. Even if I want to use an unbound form to let the user enter a new record, I’ll still use the Wizard or an AutoForm to create it as a bound form first, then clear the control source of bound controls later on.

I try to keep the design of a form as clear and simple as I can, using system colors such as -2147483633 (button face), -2147483640 (window text) and -2147483643 (window background) wherever possible. I use striking colors sparingly, if at all.

If I need a lot of controls, I’ll use a tab control to divide the controls into logically related groups.

I (almost) never use datasheet forms, I prefer continuous forms since they give me more control.

I almost always reuse a lot of code from earlier databases. I have a collection of code snippets, modules and class modules for this purpose.

Reply | Quote

Hans,

Wow, a peek into the mind of a genius .

Thanks for your response. On the note of keeping code snippets, etc., I also keep copies of my databases, however do you know of an easy way to export all VBA code from a database? I’ve searched on the forum, but I haven’t been able to find the necessary command (more presumably Add-In), to do so…

Reply | Quote

I seldom feel the urge to export ALL code from a database at once, if I have some code or module that I may want to reuse, I’ll copy it into a utility database.

(You can create a report containing all code from a database using Tools | Analyze | Documenter; click Options in each tab to specify what you want to include in the report). You can then export the report to a .txt or .rtf file)

Reply | Quote

Hans,
Thanks for opening my eyes to that whole Documenter utility…I didn’t even know it existed!
I’ve grabbed numerous sample databases, now I’m just trying to understand HOW they do what they do… (I’ve actually been trying since I started working with Access, but this lounge has helped me at MANY times). Thanks again!

Reply | Quote

Another thought as I was walking home. Use the 3 P’s:
Planning
Planning
Preparation

Ok it is two but the planning aspect is really really important. I always see application development as backward engineering. Always ask the end user what they want to get out of it at the end result first nswer is normally ” A list of…”, “a report showing…”, “A download in excel of…” Using good clean open questions you can really get to know what they really want. On each question ask another one ” What do you want to show on the report..” This can really help with the requirements and table design.

I have found this approach really helps me start to put a database together and start putting concepts together.

Once I have designed a concept I show a raw database to the user and then ask the fluffier stuff, what colour, what menu, who is your inputter etc NEVER put bells and whistles on at this stage as they will always want more!!!!

constantly review and ask to meet if possible regularly through development if you can and at major staging points so that you can work ahead and not have to go back and make major unwanted application changes.

I will get more soon

Reply | Quote

Another top tip to add to Hans’s list.

To save me having a stack of databases containing the code I create a folder with subfolder structure. When i have a piece of code that I think would be useful I copy it too text file and pop it into the corresponding sub folder for late ruse if required. If I find a database with a nice form layout or gizmo I treat this just the same but use the compact and repair menu and then zip it and put into a relevant folder in my structure. You can’t go wrong with re-usable code.

I have found that my databases tend to have my “signature” written all over them as I use tried and tested functions and form formats. I tend to use continuous forms in my subforms, as Hans said it provides you more control and provides you with footers which allows you to use calculations in text boxes (say for totals) which can the be picked up on the master form.

If any of my students come up with forms that have excessively bright colours they get a brick thrown through their window….on fire. You have to take into consideration the user who will be using it for long periods, so stay conservative, use contrast well but not excessive…. may have a few more will pass them on as I think of them

Reply | Quote

>I try to keep the design of a form as clear and simple as I can, using system colors such as -2147483633 (button face), -2147483640 (window text) and -2147483643 (window background) wherever possible. I use striking colors sparingly, if at all.

As I hadn’t managed to stumble across (all) these numbers before, I found this very helpful. Do you know of any way to set the colour choice of the “left hand margin” in a continuous form?

Reply | Quote

Do you mean the record selectors (on the left hand side in the screenshot below)? That’s governed by the ‘3D Objects’ color setting in the Windows color scheme.

Reply | Quote

Thanks for the confirmation.

Reply | Quote

I tend to work a lot like Hans, although I also spend a certain amount of time determing the primary data objects that the database is built around. For instance, a sales management database has some different data characteristics from a student database, although both may contain information on contacts, names, addresses, etc. The overall purpose of the database has a lot to do with how the data structures are designed, although a good deal of the code and even some of the forms might be shared between the two.

Reply | Quote

Another suggestion :

I use Visio for building a data model for the database. The big advantage of using Visio is that you can then use it to create all your tables automatically in Access. If you find you have to make a change to one or more tables, you just update the Visio model & then re-create the tables in Access (assuming you have not populated them with data of course).

Nick

Reply | Quote

Visio is a wonderful tool for designing data tables, but the forward engineering feature is now only available in the Enterprise version, not in the Professional version.

Reply | Quote