Normalization for large amount of data

[kvollene]

I am creating a large database for my department that will contain the information on students in 3 different majors. I am new at using Access but am learning quickly. I previously created the whole database only to find out that it did not function. I was told my a different forum that I did not normalize my data and needed to start over. Before I get any further into creating the form for my database I need to know if I am on the right track and have broken down my data far enough or if I need to "normalize" it more.

I have attached a word doc that shows screen shots of all of my tables and the form that I have started, which I am not 100% sure I am doing right either.



Any help is greatly appreciated!
Thank you
Kristin

kvollene - 2016Database chart.zip

[orange]

I suggest you research Normalization and work through a tutorial or two.
Here are 2 tutorials from RogersAccessLibrary that--if you work through the tutorial - you will learn about Normaliztion and table design. You will learn about relationships and the value of a complete description of the "business" and a data model. You have to work through them, but you will experience a procedure that can be used with any database. Each tutorial will take 30 - 60 minutes.

StudentInfo tutorial
CateringBusiness tutorial



Just a couple of comments on your attached document.

It appears that you have given a good effort. However, it seems to be overdone and perhaps too detailed. I don't know if you built a data model, or if you have a solid description(set of requirements) of the "business" involved. You can build the data model with pencil and paper, and test it with some sample test data.

Creating a set of tables in any database management software is NOT the same as analyzing the requirement, identifying the business rules (the research to determine how the "things" you are dealing with interact with each other). You will find, after working though the tutorials, that building a data model and vetting/validating it against your requirements will lead you to a blue print(data model) from which you can develop the solution in Access.

There is a difference between database ( concepts, techniques,design, testing, best practices...) and Access ( a software to assist the rapid development of applications).

Don't get me wrong --this is not trying to be negative. I am offering some constructive advice because you said you have done a detailed attempt at database previously that came up short. There are a number of free data models at http://databaseanswers.org/data_models/index.htm
Look through a few and see the level of detail and the relationships between tables.

Every table should have a Primary Key which uniquely identifies each record in that table.

Good luck.

[kvollene]

Thank you for your advise and guiding me to the tutorials. I have worked through them and think that I have broken things down far enough like they said but I have one more question for you. Did you feel that the database I attached to my original post had too much data or was it just the normalizing that made it wrong? I am questioning if I am thinking about it all backwards. I am thinking about all of the information I need visible in my form so that other people can enter data, which is why there is so much info listed, is that backwards?

[orange]

I don't think Normalization and large amount of data are some sort of combined issue.
You say you have a lot of data, but readers don't know the volume of that data.

My point regarding normalization is that we do not know your "business rules" or intended use of the tables involved.
In the tutorials I suggested, the author starts with a business issue/opportunity that is clear described is simple terms.
It is by understanding your business and the things involved that you design your database --tables and relationships -
to support the business. In your attachment you show tables and field names at a detailed level, BUT readers do not understand
how those tables relate to one another, or to your requirements.

What is your database about in clear, simple terms (no jargon)? What information do you need to retrieve/report from the database? That will
give you a focus on what information you need to collect and maintain.

Good luck.

[ssanfu]

Looking at your word doc, I am a little concerned about your fields. Specifically pages 4 - 8.
There are field names like "MSNC5310TheoreticalFoundation", "MSNC5310Semester", "MSNC5310Year", "MSNC5310Grade". To me, these field names like these are actually data.

I can't actually count the number of fields in table "MSNCCourseTbl", but it looks like there should be about 6 fields:
An autonumber PK field, LamarID (FK to student??), Course instructor, Course Semester, Course Year and Course Final Grade.

The problem with the current table structure(s) (IMO) is that the dB has to be redesigned if a new course is added or a course number is changed.


--------------------------------------------------------

More info to read:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


I always use an autonumber type field as the primary key field in my tables.
So here is info about autonumbers:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers



You might also want to read:
Fundamentals of Relational Database Design
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf



My $0.02......

[kvollene]

Okay, first, I greatly appreciate all of your help without making me feel stupid like the last forum I was on. Thank you! Second, I have taken all of the information you have given me and done a lot of thinking while trying not to get frustrated. I feel like I had a break through and got things right this time (I HOPE). Attached is a spreadsheet showing a table layout I did. Please let me know if it makes more sense and if I did things right this time. I am not 100% sure on the PK but I believe everything else should be correct.

Again your help is appreciated.

Apparently I cannot attach items again so there is a screen shot below. I hope you are able to see it alright.

Kristin

[ssanfu]

Apparently I cannot attach items again

You should be able to attach items again.... click the "Go Advanced" button, then scroll down to "Manage attachments".

Question - why do you have multi-field PK fields?
I always use an autonumber type field as the PK field. The purpose of a PK field is to have a unique field to link tables (for RI).


I used a couple of your tables and made a picture of how I would set up the tables.

I do not use Look up fields in tables.
See http://access.mvps.org/access/lookupfields.htm
and http://access.mvps.org/access/tencommandments.htm

[kvollene]

The PK in the sheet I attached actually are all auto numbers. I just wasn't sure if they are all supposed to be PK or if some should be FK. I understand now by looking at your design. Thank you for explaining the evil of look up fields...I had no idea! Is it okay to use a combo box in the form though so other users are restricted to what data can be entered?

I am going to do some work on actually creating my tables and then will post the database with what I have done here so you can look at it and tell me if I did it right.

Thank you so much for your help!
Kristin

[ssanfu]

Is it okay to use a combo box in the form though so other users are restricted to what data can be entered?

Yep. That is the preferred (and only) method (IMO).

Keep working at it...... and enjoy the weekend..