Results 1 to 14 of 14

Database planning

  1. #1
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    162

    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
    6,276
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  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
    14,287
    An Introduction to Database by guru99 for beginners or a reference/refresher for others.

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

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

    Other materials from RogersAccessBlog.

    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.

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

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


    Here is
    -a link to an excellent tutorial on database design by Barry Williams
    - link to several free, generic data models from Barry Williams' site.

    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

    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

    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

    An older youtube video series by Richard Rost(599CD) on Introduction to Access. It's 2010 but still relevant.

    Here are a couple of 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


    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.
    Attached Files Attached Files
    Last edited by orange; 11-05-2019 at 08:30 AM. Reason: Update to BA_Experts free courses

  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
    2,896
    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
    162
    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
    6,276
    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
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  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
    14,287
    @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
    6,276
    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
    14,287
    @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
    2,896
    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
    6,276
    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
    14,287
    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
    25
    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
  •  
Tech Forums: Microsoft Office Forums