Results 1 to 14 of 14
  1. #1
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183

    Database planning

    What do people use to plan their databases? Is there a good free source that I can use to map out the info I want to collect and relations etc.




    Sent from my iPhone using Tapatalk

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    One way is to not reinvent what already exists, so there might be a relational model that resembles what you need.
    http://www.databaseanswers.org/data_models/

    Then there is the entity-relation approach:
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    For table design, I use Excel to create a layout to represent table names, field names, data types, defaults (if any) field sizes, whether or not they're PK or FK fields, etc.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    --Some of these links are "older" and may no longer be active--- Last update Feb 2024

    An Introduction to Database by guru99 for beginners or a reference/refresher for others.

    Fundamentals of Relational Database Design (Paul Litwin)

    If you are thinking about Ms Access for a database project, spend 1/2 hr reviewing this video Oct/22 by Daniel Pineault What you need to know before you get Started

    Here is an OER(online educational resource) book Database Design 2nd Edition (Adrienne Watt and Nelson Eng)

    Here is a link to some great database design info from M$oft

    Access History and evolution

    Access Basics by Crystal -- lots of free Access info

    Colin Riddington(Isladogs MVP) has lots of articles, explanations and examples on his website
    and videos on his youtube channel

    Articles, techniques and references to other sites with specific solutions by Mike Wolfe who also provides a Week in Review email telling or pointing subscribers to current MS Access topics.

    A guided tour of Access by Mark Gillis

    Great youtube video by Pat Hartman on Access Data Validation.

    Daniel Pineault has a number of excellent videos on his youtube channel and a variety of technical articles and bug info on his website. His Access Best Practices and TroubleShooting Steps is great.

    Guiding Principles from 20 years of Programming, Coding, Project Experience by Alex Ewerlöf. Lessons learned and things to keep in focus on projects.

    Designing Table Relationships by Lisa Friedrichsen very clear overview of relationships and how they tie in to business.

    Some facts about Autonumbers. Also see the attached file UA_Autonumbers.txt

    For practical experience / learning of tables and relationships spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
    ZYX Laboratories
    Class info system
    Catering Business
    Widgets

    Other materials from RogersAccessBlog.

    (Feb 2024)
    Excellent video demonstrating the use of classes --Properties-Methods-Events-- using a Car analogy. This was developed by Siddharth Rout in Excel vba, but is easily understood and applicable to Access. He uses Car, Gear and Tire classes and knits these together to offer a sample Drive the Car experience. He has other youtube videos on various vba subjects and OOP.

    If you are unfamiliar with analysis techniques or need a quick refresher, there are several short, humorous "knowledgeKnuggets" by BA-Experts on youtube.

    Here is a longer nugget from BA-Experts on Business Requirements Data Modeling.

    Here is a link to Data Modeling 101 by GPGeorge. It is very thorough and a great learning reference. Lots of clear definitions and samples. A great tool.

    Access Diva (Gina Whipp) has lots of free tips on Access and database, tables, queries, forms etc with examples and data models.

    An overview/intro to data modelling to whet your appetite.

    A link to A.P.R. Pillai's great site (MSAccessTips) with many tutorials and examples on a variety of MS Access topics.

    More details and theory of Entity Relationship Modelling (archived materials)

    List of free courses from BA_Experts (as of Jul 2021) - good practical stuff with a touch of humour.

    ***Interesting article on Conceptual Data Modelling by Alec Smart. This is an excerpt of a Business Driven Data Modelling Webinar by Ellie Software Oct 22 2020. I'm not familiar with Ellie, and not recommending it per se. However, I have identified the presentation section by Alec in the link with start/end times. It deals with simple, business terms and approach that seems quite useful for communications. (added Feb 2021)

    (July 2022) Here is a link to some resurrected videos from an Access tutorial series by DataPigTechnologies re Queries. And here is a link to videos on Form Basics. Even though Access looks different now, the concepts and functionality persist. Thanks to owner, Mike Alexander, for making these videos available for presentation once again.

    I like to lay out a data model before getting near physical Access.
    You can build the model with pencil and paper.
    Here are some steps from Dezign for data modeling

    You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

    Info on Normalization from RogersAccessLibrary. Normalization from M$oft.

    Youtube video on Normalization and Normal forms by Decomplexify

    Here is a link to some Naming Standards by Rainlover.

    An overview and introduction to MS Access by guru99

    Steps to better VBA coding from FMS

    An extensive set of MS Access tutorials from SourceDaddy

    A variety of Access and Database articles from Roger Carlson --great resource.

    Here is the link to first part of a 2 part article on VBA programming, writing and testing by M$oft

    And this video series on VBA by Steve Bishop or this newer one.

    A youtube video series by Richard Rost(599CD) on Introduction to Access. He has recently( Aug2020) added info on a genealogy application with a lot of step by step instructions with references to other tutorials on various topics. Very good refresher or for anyone starting to put tables, queries and forms together.

    A pdf by Susan J. Dorey Microsoft Access VBA Techniques (added Aug 2020**)

    Here are some old programmer's rules that always apply (from the_Doc_man)

    1. You aren't ready to do it in Access until you do it on paper.
    2. Access won't tell you anything you didn't tell it first yourself (or at least tell it HOW to tell you).
    -more info here
    and here and also here*

    If you are a fast learner or just need a quick refresher, then research videos on Relational database
    by Dr. Jennifer Widom. She is quick, but very good.
    A few videos by her.

    Relational Design theory
    Functional Dependencies Part 1
    Functional Dependencies Part 2
    Functional Dependencies Part 3
    Functional Dependencies Part 4

    I have attached in pdf format a reference on relational database principles.

    I have also attached a description of "Stump the model" - very useful for database design. Get the players onboard early.

    Youtube tutorial videos by Richard Rost (599CD) on
    Bar Codes
    scanning Bar codes
    printing Bar codes
    and
    QR codes with Access.

    Here is a link to several example Access databases supplied by ADTejpal on RogersAccessLibrary. These are not applications, but are samples illustrating how the problem might be solved and are therefore intended for educational use.
    Attached Files Attached Files
    Last edited by orange; 03-11-2024 at 06:41 AM. Reason: Update to sources of info/ADTejpal samples link/Isladogs

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Like @orange, I like to go really hi-tech for this...I use a pencil, a legal pad and index cards! The design outline goes on the pad, and I use a card for each Table, Form, Report, etc.

    An hour of planning, like this, is worth a day or more of muddling through, in Access itself, laying all of these things out!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    I think I've really getting the hang of it now after watching some of Bishop's videos. I think my confusion still remains as to which table gets the FK and which one to store the actual value. Any tricks to making sure you're storing the FK in the correct table? Let's say i have tblOrders. Then I have tblOrderType. Would I have OrderType_ID in the orders table OR put the Orders_ID in the order type table?

    That's seems to be my biggest obstacle right now in the development stage.


    Sent from my iPhone using Tapatalk

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Would I have OrderType_ID in the orders table
    Yes. Try to think of it as an ENTITY - ATTRIBUTE relationship. If the table is about an entity, the fields are attributes (tblCust: customers have attributes, FName might be one).
    However, if the attributes are stored in their own table as you describe, the main table doesn't repeat that text or numeric value, it holds the PK of the attribute table instead. Using my same example, tblCust.CustType would show 4, not the 4th text value from the attribute table. 4 would be a FK in tblCust because it's a PK in tblCustType. Hope that's not confusing.
    Last edited by Micron; 03-27-2017 at 02:40 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Thomas is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Location
    Danville, VA
    Posts
    5
    Lots of good information. Looks like I've got some "Learning" to do. I'll be back...maybe with a better idea of what the heck it is I'm trying to do. Thanks to all.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    @Thomas,

    I really suggest the tutorials from RogersAccessLibrary mentioned in the link. You have to work through 1 or 2, but you will learn.
    And what you learn can be used with any data base.

    Good luck.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    I'll be back..
    Seeing as how you waited about a year to come back to this, forgive me if I don't "leave a light on for you"

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    @micron,

    jeez! I didn't notice the dates YYYY-- I saw Mar 27........... gettin' old.

  11. #11
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Micron View Post
    Seeing as how you waited about a year to come back to this, forgive me if I don't "leave a light on for you"
    Thomas was not the OP for the thread...apparently just someone else who found the thread helpful!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    See? Orange isn't the only one who's not paying attention!

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Good point Linq! Seems Thomas was just passing through and landed on this thread and responded.
    I often refer to this thread --post #3 because of the collection of links and articles.
    I also note that I have updated that post a number of times since it was first made--adding info/links etc.
    And as per Bart Simpson
    I must pay attention.I must pay attention.I must pay attention.I must pay attention.
    I must pay attention.I must pay attention.I must pay attention.I must pay attention.
    I must pay attention.I must pay attention.I must pay attention.

  14. #14
    Dave Lambert is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    26
    Thanks Orange! I put most of the links in my favorites and will be looking at them. They will be very helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Planning timeline
    By paultje_bos in forum Queries
    Replies: 5
    Last Post: 09-07-2016, 03:50 AM
  2. Help with Database Planning
    By dgaletar in forum Access
    Replies: 12
    Last Post: 03-21-2014, 10:31 AM
  3. Help with Table Planning in Student Database
    By whitelexi in forum Access
    Replies: 5
    Last Post: 03-09-2014, 03:08 PM
  4. Diary Planning Database?
    By Sawyer05 in forum Database Design
    Replies: 1
    Last Post: 02-16-2012, 06:27 PM
  5. Planning my first Access DB
    By swankinrosco in forum Access
    Replies: 11
    Last Post: 01-30-2012, 07:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums