School Database (1 Viewer)

Rickochezz

New member
Local time
Today, 02:08
Joined
Jul 15, 2017
Messages
3
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
 

Attachments

  • Relationship.jpg
    Relationship.jpg
    102.7 KB · Views: 174

plog

Banishment Pending
Local time
Today, 04:08
Joined
May 11, 2011
Messages
11,611
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])
 

isladogs

MVP / VIP
Local time
Today, 09:08
Joined
Jan 14, 2017
Messages
18,186
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/showthread.php?t=66521&page=2&p=360858#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
 
Last edited:

Rickochezz

New member
Local time
Today, 02:08
Joined
Jul 15, 2017
Messages
3
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
 

Users who are viewing this thread

Top Bottom