Since you said "My wife and I will want the database to be as comprehensive as possible", here is more to process...I am still working on processing and applying what I'm told here on our database.
Have fun... hope your head doesn't explode!!
Since you said "My wife and I will want the database to be as comprehensive as possible", here is more to process...I am still working on processing and applying what I'm told here on our database.
Have fun... hope your head doesn't explode!!
You are probably overwhelmed at this point! Go back to basics:
1 - on a piece of paper draw some buckets
2 - label the buckets with what you are going to store in them and draw the links:
a) "master" tables - tables where the data doesn't change very often:
(i) Students
(ii) Teachers
(iii) Courses
(iv) Current course schedule (can contain course ID, course number, course date and time, max number of students allowed)
(b) "transaction" tables - tables where data gets put in there over and over, each time "new/history" data, and link them to the other buckets:
(i) which students are signed up for which courses, attendance and results/grades - link to Students and Current Course Schedule
(ii) which teachers are taking which course - link to Teachers and Current Course Schedule
(iii) payments - each time a student makes a payment - links to Students and Current Course Schedule
3) Study this picture and see if all your questions are answered
4) Start going into detail of what exactly is going to be stored on each table (some tables may only be "junction" tables containing pointers to other tables, such as Teacher/Current Course)
5) Now you are ready to create the tables in Access
At this point you will need to know about naming conventions. You are a "techy" at this point, not a user, so grammatically correct is not required. Some important points: no spaces (use underscore instead), no funny characters (only letters and numbers), no Access reserved words (such as Date), tables to be either prefixed or suffixed by "tbl" or "t" or some such designation. Name the fields with words that make them immediately identifiable and always use the same name across tables (e.g. StudentID will be called that on every table).
I know you are being swamped with info, but we're all trying to help you with your project and your introduction to database.
Here is an older post I found while searching for "related data". There are links in this post that may help with concepts and approach.
Also, I found a jpg of a data model created to help a poster and have attached it. (Could not find the original post/thread???)
Hope it helps.
Good Tuesday evening, please don't think I'm being swamped with information. I feel so blessed getting this kind of help FREE!! My concern is, not getting too much info, but how to apply what I've learned here. I am so thankful that even at 80 y/o I still have a mine sharp enough to learn and being excited learning what what you all teach here. I am very excited and motivated, so don't stop helping me. My concern now is, I've really got to look at every aspect of how to put everything together. As you all have suggested, I don't want to have to try and go back to make changes. I've got to get it right the first time. Moderator, your StudentsTermsSubjects:ER_Diagram_116 is also a big help. I just have to figure out all the terminology. I will check out the links you sent as well.
Hello all,
I am picking up great info each time you all send additional experiences to chew on. I am just aware that I've got to think about the fact that some of these students for our Real Estate classes will return later, especially those who must take a certain number of classes to meet the Georgia State Real Estate Board's requirements. Each student has to have a certain number of CE"s in order to keep their real estate license. That means that some will be returning for Code Of Ethics, Post License, and other classes. Plus, later we may be adding classes. Will adding classes later be a big problem? I am learning more about the "One to many and one to one, etc". It's a little confusing going in now. This could be a very simple database if I did not have to search for various info down the line. Thank you all so much. It may take me a little longer but I will eventually get it.
Hello all, Sorry I have not gotten back to you til now. Had death in family. You all have been just wonderful regarding your welcomed advice and guidance. After your guidance, I slightly jumped in and constructed a "Student Table" that includes the following in the order presented:
StudentID
FirstName
MiddleName
LastName
Address
City
State
ZipCode
HomePhone
MobilePhone
WebSite
StartDate
StudentGrade
Note
I am also thinking of the additional Tables:
StudentClasses
InstructorStaff
ExamResults
StudentGrades
ClassDates
Now, I wonder if I've already included "Student Grades" in the "Student Table" is setting up a seperate Table for Student Grades needed? Also, right now there is only one real estate teacher, but there may be one or two more down the road. I also want to include a Calendar and be able to create invoices for each student that will also include the grade or whether they passed the test for a particular class. By the way, here are the present slate of Real Estate Classes we plan to present right now and there will be a table for these:
Pre License pt. 1 (37.5 hrs)
Pre License pt. 2 (37.5 hrs)
Fair Housing (6 hrs)
Georgia License Law (3 hrs)
Property Value (3 hrs)
Ready For Real Estate As A Career? (3 hrs)
Post License (25 hrs)
Let's Go Figure (3 hrs)
Code Of Ethics (3hrs)
What have I left out before I go any further. Again, I've only completed the "Students" table. I would be pleased with additional guidance. Thank you so much.
Bob
You should take orange's advice and design the table structures/relationships using pencil & paper, whiteboard, large piece of cardboard, chalk and the driveway, etc BEFORE jumping into Access. You will save yourself time and stress.
In your "Students" table: "State" is a reserved word in Access and shouldn't be used for object names. I use "ST" or "StateName".
Also in the "Students" table, you have a field named "StudentGrade". Is "StudentGrade" an attribute of a student? Eye color, yes. Hair color, yes. Height, yes. Grade..... No.
And grade for which course? What happens if the student takes many courses? Which course is the grade for?
"StudentGrade" IS and attribute of the course attended, as is Course date. So "StudentGrade" should not be in the "Students" table, but in a different table....probably in the "StudentCourse" junction table. (because the relationship between students and courses is a many-to-many relationship.)
Good Wed Morning Sir,
Again, I stress my total lack of knowledge of Access and the need to learn enough to construct a working Real Estate School for students who interested in becoming a realtor, or anyone realtor who has to take continuing educational units to meet Georgia Real Estate requirements. I surely did pay attention to advice offered by Orange and all others here. However, it was still very difficult to design the needed database even though I used pad and pencil to draw the model. I've only gotten to where I am at this point because of everyone of you who has taken time to give me guidance. I appreciate your info re: the Student Grade Table. I will make the change you suggest. You're right, I will have to figure out how to develop a separate Table for Student Grades. I don't know how to do that yet. Again, drawing out an example of what the database should look like then developing a working database has been difficult for me. I will keep trying though with everyone's support here. Again, I want to thank all here for your wonderful help.
Bob
Bob,
Did you work through a tutorial from RogersAccessLibrary?
Further to Steve's comments and your current activity:
A Student could enroll in Many Courses
A Student could take a Test in a given Course and obtain a Grade in that Course (for that Test)
The information about Student Grade would be recorded in a separate Entity/Table that would involve
-the Student
-the Course
-the Test (could involve semester/date of Test..)
-the Grade/Mark
similar to my StudentClassTest in the model in post 18.
Good luck
Good Wed afternoon Orange,
What was very eye opening for me was, "Relational Database Principles, "Donal Ravey 2008". I've got to re-read that again. However, as I read, the concept he is presenting begin to become more clear. Such terms as, normalization, atomic and "class of things", become a little clearer for me and not as abstract as before. I totally missed this, but just found it among the info you sent, and I have got to thank you again sir. As you and everyone here as cautioned, I want to make certain as I can that I construct the database as comprehensive as possible. I am going to do some more "working through".
bob
Not trying to overwhelm you, but here is more info that I frequently review/re-read.
Feel free to ignore this.......... (or read at your leisure)
Good Thursday Morning All,
I'm eating this info you are providing up and in no way am I being overwhelmed. I can't thank you all enough for taking of your important time to help me and many others. Someone mentioned a while back that I may not know our business as well as I should. That very well may the case. I say and admit that because building a database for our brand new school makes for a task I was not prepared for. Many real estate schools are not like a regular learning institute. There are only about 10 - 14 mandatory courses realtors must complete in order to maintain their real estate or brokers license. While I can readily sense the more obvious Tables required to set up my database there are other areas that I can't sense. Right now and because of each of you, I understand I require the following "Tables"...
1. StudentID
2. CourseID
3. StaffID (right now my wife is the only instructor)
4. StudentGrades (75 is passing)
Now, I wonder if I need a separate Table for Enrollments, Monies Paid by students, Attendance, Test Results, etc. Plus, I think I will require a class calendar and a means of creating invoices, reports, and forms. As was suggested right here, my wife and I will want to be able to look back from when we started the school to see what our progress has been. Based on information you all have provided me, I at least know how to get started and that's great! When I came here, I had no clue and that's truth. I really can't thank you all enough. I feel very blessed to have found you all here. While I am moving very slowly, because of everyone here I feel blessed I am moving forward at all. Again, I can't thank you enough for all the help you've already given me.
bob
Bob,
You have to determine what is in scope (what will your database be about; what business processes will it support) for your database.
If students can be enrolled in courses, then that will have to be represented in your database design. Similarly, if you plan on collecting and managing money/fees--payments, partial payments, method of payment; student balance etc - then that also must be represented in your database.
Good questions. Make a list of all possible; then determine what MUST be included; what should be included; what could be included. Then design based on your list. If it's in your overall plan, you can build based on priorities --even make some changes --but you'll know where all the pieces fit in your plan/model.
Good luck.
Hello again Orange and all of my helpers here,
Of course you're correct regarding your advice here. To be perfectly honest sir, I am afraid I will either leave something important out or, add something that will not benefit the database. My primary fear is, setting up a table for example that may not be required. So far here are the tables I've come up with......
Student: (All students listed with address, phones, email/web addresses, etc)
Class/Courses: (list all 9 classes we have scheduled so far) can I add others later?
StudentGrades: (This will be an empty table until students complete a course). Also, right now we are scheduling classes as we get student. Once we start getting students on a regular basis, we can schedule classes. Would it be wise to schedule classes now even when no students have shown an interest now?
Staff: My wife is the only instructor presently. Should this be a Table as well? Should I still do a Table even with one (1) instructor?
ExamResults: I'm not sure this should be a separate Table? Maybe I should do a "TestScores" Table?
StudentHistory: I thought about this Table but am not sure what would go there.
Invoice: Should this even be a Table?
Enrollments: This Table would include StudentID and ClassID
Right now that's all I can think of that might be included in the Tables. However, I don't want to make this database too complicated. Is it possible to start out and then add Tables later?
Once my wife (who is the realtor/broker) was certified to start the school, I went on the internet to see if we could find businesses that could provide all the support and data we would need, plus, accept payments from students. They would install a link on our website permitting potential students to go there, pick a course and pay for it. Everyone of these businesses I looked at and contacted wanted to sign a 10 to 15 page contract with lots of very small print. However, these contracts wanted a commitment of at least 3 years and of course they would require us to pay a certain fee for each student which was more than we could afford just starting out. Plus, I never want to sign a contract especially a 3 year contract. So, having lots of recent experience with Microsoft's Publisher and a very limited amount of experience with Excel many years ago, I decided to try constructing a database using Access 2007 which I understand is not that much different from Access 2010 and that's great! Well, that's the long and short story everyone. I was forced into finding out about and using Access. Mind you, I'm not upset since I believe this is one of the best things that could have happened. I am going to keep at this until I learn as many of the pro's and con's as possible. Access is a very powerful and useful tool.
bob
Good Friday Afternoon All,
We're going to have a great weekend here. The temp will be around 70 degrees over the next 7 or so days and I love it. I just wanted you all to know I am really paying close attention to "Entity Relationship Diagramming". I've just been reading and trying to let the various ways to look at this information sink in. Thank you for leading me in this direction. I am sure I will have to look hard at some of the other information you all have presented, but for now, I am trying to take it a step at a time and not trying to cram too much at one time. Thanks again all.