Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Steps to designing a database (All)

    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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Steps to designing a database (All)

    Hi Jeremy,

    That's a rather broad question! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Steps to designing a database (All)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    Hans,

    Wow, a peek into the mind of a genius <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    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...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Steps to designing a database (All)

    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)

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    Hans,
    Thanks for opening my eyes to that whole Documenter utility...I didn't even know it existed! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15>
    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 <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> times). Thanks again!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Steps to designing a database (All)

    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
    Jerry

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Steps to designing a database (All)

    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
    Jerry

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Steps to designing a database (All)

    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.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Steps to designing a database (All)

    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.
    Charlotte

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    >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?
    Gre

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Steps to designing a database (All)

    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.

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Steps to designing a database (All)

    Thanks for the confirmation.
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •