Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-15-2017, 10:37 AM   #1
Rickochezz
Newly Registered User
 
Join Date: Jul 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Rickochezz is on a distinguished road
School Database

Hello All:

I'm new here and I have a school database that I have created with limited Access knowledge but am learning at a ridiculous pace.

I seem to be running into various problems and issues as I progress and attempt other "features within the database"

I have attached the relationship table for any who would like to take a peek in order to provide any assistance.

The tables on the right that have no linkage at the moment are to be deleted once I figure on how to properly handle the Attendance and Marks by moving them out of those "individual" courses that I originally have them.

Any assistance and / helpful tips are always appreciated and please keep in mind I am still fairly raw at this stuff and the reasoning "why" I do the things that I do programming wise make sense to me at the time of implementation.

Thanks
Rick
Attached Images
File Type: jpg Relationship.jpg (102.7 KB, 71 views)

Rickochezz is offline   Reply With Quote
Old 07-15-2017, 12:19 PM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,115
Thanks: 10
Thanked 1,950 Times in 1,911 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: School Database

First, for being raw at this, you did a pretty good initial job--someone did their normalization homework. Second, its a lot of data so I will just hit big issues that you might have throughout your database, but I'll just use one example:

1. Don't store values in field names. Usually this means you need a new table with a special field to hold that value. Using [OtherInfo]--all those _Skills fields should be in a new table. That table would be something like [Skills], it would have an autonumber ID ([SkillsID]), a foreign key to the OtherInfo table ([OtherInfoID]) the text before the skill would go into a type field ([SkillType]) and if the the actual values currently in the table are not Yes/No values they would go into the same type field ([Skill]). If they are Yes/No fields you wouldn't need a field because the presence of the record in the table would mean 'Yes'. Again, one example, check your other tables for this too (hint--Ident table has a ton of _Returned, _Submitted, _Requested, etc fields).

2. Storing redundant data. I see a [Sponsor] field and a [SponsorID] field in Student--you only need to put the [SponsorID] value there and its connected to the [Sponsor] table, with it like that you can access all the [Sponsor]
data. I see [StudentStatus] in two places as well.

3. Improperly linked tables. This sort of goes hand in hand with #2. I see FeePayment has [StudentID], [SerialID] and [CourseID] yet the only relationship is to the [Student] table. Why are those other IDs in there? I see [Serial] table has [CourseID] as well, that's incorrect since [StudentCourse] has those same fields as well. Actually, I see this in a lot of tables. You really need to figure out how those 3 tables relate and eliminate unnecessary ID fields.

4. I see a [CreditCardNo] field in [CardHolder], think twice about storing this. For one, its scary to be responsible for that information on your system and two--it might go against the terms of your credit card processor agreement.

5. Uou don't need a fiscal year table--it's calculable. So instead, build a function--you pass it a date, it returns the fiscal year that date is in.



I would work on all those issues, then let's work on ading those other 8 tables to the mix. In fact, you may subtract a few you currently have if you can't figure out where they go (eg. [Serial], FeePayment])
plog is offline   Reply With Quote
Old 07-15-2017, 12:20 PM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,051
Thanks: 37
Thanked 483 Times in 448 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: School Database

Hi Rickochezz

I write databases for schools so in principle I can assist.

However I am aware you have several active threads related to this on AccessForums.net and at least one on Utter Access. You should have mentioned that.

In fact you have been referred to a thread on AccessForums where I gave a detailed account of part of my db related to assessments.
My username there is ridders52.
The link is http://www.accessforums.net/showthre...858#post360858
See post 12

You can download a demo database from my website -click on link in my signature line below.
It contains example data for a fictitious school.
Its an accde file so you can't view the code but it may give you some ideas

With regard to your relationships window it looks similar in complexity to my main schools database.
However there too much on display for me to make any detailed comments at this point

If you wish to ask a more targeted question, that's fine.

However, follow the etiquette related to cross posting on each site.
That is explain that you are doing so and provide a link.

EDIT - just read plog's comments and agree with every word

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!

Last edited by ridders; 07-15-2017 at 12:32 PM.
ridders is offline   Reply With Quote
Old 07-15-2017, 01:19 PM   #4
Rickochezz
Newly Registered User
 
Join Date: Jul 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Rickochezz is on a distinguished road
Re: School Database

Thanks guys - a lot of those points make sense - I will definitely start to get those issues corrected and then see where we are at after that - really appreciate it that you took time to go through the relationship table for me. More questions will definitely come. As you may be able to see, I am coming from an excel background so sometimes it is hard to see the database side of things at first. I'm always willing To see a proper point of view. I'm trying

Rickochezz is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
School Database system wabamdo Tables 3 08-28-2014 11:42 PM
School database dammy Forms 7 03-25-2014 06:41 AM
School database syedadnan Queries 2 04-03-2013 06:37 AM
school managment database Ino Tables 1 01-08-2013 02:28 PM
Help in creating school database Noory Tables 4 11-29-2011 06:24 AM




All times are GMT -8. The time now is 11:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World