Results 1 to 9 of 9
  1. #1
    adame is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9

    Organizing my data in table - normalization?

    Hello,

    I am quite new to this and am in the process of developing a student database at department level (e.g.) Chemistry.

    I have student table/staff table/module table etc, and things are working fine.

    Now I would like to enlarge the database to cover other schools, e.g. Physics and Biology, so different students, modules, staff etc and, on the main form, to be able to switch between schools via buttons.

    What is the best way of incorporating the additional schools/students/staff data – is it best add additional data to my existing tables and structure and define by ‘school’ or would it be recommended to set up additional tables/relationships etc for the additional departments.



    Many thanks,

    Adam

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    TSchool table would have schoolID (auto).

    the tClassYr table would have :
    classYrID (auto)
    ClassYr. (2017)
    SchoolID (long)

    the tClass would have:
    ClassID (auto)
    classYrID. (Long)
    ClassName. (Chem101)
    Teacher
    MeetDay
    Etc

  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,716
    adame,

    Step back and identify what your new database is about. Identify the things and the attributes of those things that are relevant to you and your business. With pencil and paper create a draft model that represents the facts you have identified. Using some sample test data, mock up some contents of your proposed tables, then test some scenarios representing what you need from the database and confirm that the database structure supports your requirements--adjust and retest as necessary.
    This will help you build a blueprint for your database.

    Good luck.

  4. #4
    adame is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9
    Thank you that makes sense!

    Quote Originally Posted by ranman256 View Post
    TSchool table would have schoolID (auto).

    the tClassYr table would have :
    classYrID (auto)
    ClassYr. (2017)
    SchoolID (long)

    the tClass would have:
    ClassID (auto)
    classYrID. (Long)
    ClassName. (Chem101)
    Teacher
    MeetDay
    Etc

  5. #5
    adame is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9
    Thank you, you are right, I have done this but clearly not in enough detail!

  6. #6
    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,716

  7. #7
    Janee Koehn is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2015
    Posts
    8
    I hope this could still be useful for you...

    http://www.dummies.com/programming/d...ysql-database/

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For your light reading.....

    Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts
    Normalization http://www.utteraccess.com/wiki//Normalization


    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.html
    What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


    Entity-Relationship Diagramming
    ==============================
    Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
    Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
    Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...ming-part.html
    Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...-i-ii-and.html


    The Normal Forms
    =========================
    The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
    The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
    The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
    The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
    The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html




    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

  9. #9
    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,716
    I totally agree with Steve's list, and if you are a fast learner, watch this series on Relational Theory by Dr. Jennifer Widom on youtube.(she is fast, but great info.)
    Last edited by orange; 02-18-2019 at 08:01 AM. Reason: revised link

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

Similar Threads

  1. Replies: 2
    Last Post: 01-12-2017, 05:56 PM
  2. Help organizing a table or tables
    By CharissaBelle in forum Access
    Replies: 10
    Last Post: 05-19-2016, 02:19 PM
  3. Re-organizing records in a Table
    By swb1 in forum Access
    Replies: 1
    Last Post: 07-28-2014, 05:03 PM
  4. Need help re-organizing data
    By saccolicious in forum Access
    Replies: 2
    Last Post: 10-13-2012, 07:15 AM
  5. Replies: 17
    Last Post: 01-12-2012, 11:25 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