Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Smile Unanswered: Not another attendance type question? Sorry, but it's relatively basic

    Good morning, evening or any other suitable timeframe greeting dependent on your location.

    I'm right at the beginning of designing a class attendance database. To wit, I have no tables, no code, no models or anything. I want to get clear in my own mind the best way to model the data I need, rather than just plough on into Access and start building something which ultimately falls down. Here is my quandary -

    Each course has a number of modules. Each module may run 3 times per year. Each module is taught to many students. Each module is taught via lecture and practical sessions. Thus far not too difficult. However, each practical session may only contain a fraction of the students registered on that module. Practical sessions may be taught at the same time of day, on the same day of the week, in two or more different locations, each with their own lecturer. Suddenly my modelling brain turns off, goes off into the corner and sulks.

    I am stumped on the best way to model this relationship. Perhaps I could create a group ID for each sub-group being taught? That way I wouldn't be dependent on the lecturer remaining consistent or the location remaining consistent. As I say, I'm not looking for someone to spend hours building examples for me, I would simply like to discuss the modelling aspect if anyone could gently (or not) lead me in the right thinking direction.

    I'm sure once I start trying to build it I'll have all sorts of other problems, but that's not the point here. By the way, I am a student at a university, but I have basically finished my course now and am beginning work on my project, so this is not for a homework assignment or whatever. This is actually because I noticed the long-winded process the university currently uses to record class attendance and suggested they make it simpler for the lecturers - and thus someone has told me that if I can do it then they'll implement it (albeit they will take the design and put it into an Oracle DB no doubt). As I'd like to curry favour in the institution in the eventual hope of finding time to do a PhD there in robotics, I don't want to fail too early in my efforts. Many thanks in advance for any help offered.
    Regards,
    Gordon

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hello, Gordon, good luck on the robotics PhD, and welcome to the forum.

    I'll start you thinking here, but you definitely have your work cut out for you. You can't put all the data into a single table; you understand that. There's simply too much data, and the table would be worse than unwieldy. So, how do you decide how to seperate the tables?

    Well, you have to figure what kinds of "like data" you have in the project. You have, for instance, courses, students, lecturers, locations, times, dates.... Do I make my point? Some of these can be combined into a single table, such as courses, locations, dates, and times.

    However, the student table (tblStudents) must also have a relationship with the courses (tblCourses) table, in order to know how many students (and who they are) are registered for a particular course. Similarly, the lecturers (tblLecturers) have to have a relationship with the courses table for obvious reasons.

    I haven't given you a comprehensive look at this, by far. I admit to not understanding the entire scope of the project. However, you should be able, with BS&T, to ferret out all the different types of data you need to seperate into seperate tables.

    Now, I anticipate you asking, "Why get so detailed? It's so much hard work!" You're right, but if you want to create a usable and viable whole, it more than pays. Why? Because with all these seperate tables with unique data, you can use them for much more than this rather focused purpose. You can, for example, expand the students table to include which/how many courses the student took, credits earned, tuition paid, degrees earned..... ad infinitum. Of course that goes for lecturers as well, to determine tenure, payroll..... information. You dig?

    Good luck, and if you have questions (you will) don't hesitate to ask. Oh, by the way, you MUST familiarize yourself with the help file. And use it - liberally! It's not optional nor a luxury!

    Sam

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Eww... Oracle

    I have to say, as Sam pointed out, the helpfile in access is actually pretty good.

    You'll probably want to look at database normalisation, as well as getting your tables into Boyce Codd normal form (SF3.5, as some people call it).

    When planning these two things (although, basically the same), are so important. You'll find if you don't get your table setup correct from the beginning, you'll encounter problems further down the road, which can result in editing A LOT of, potentially complicated, code.

    Take 4 hours or so just to read up on these topics. I didn't find them incredibly difficult to grasp, but other people I was studying with couldn't get their head around it very quickly. If you've got a logical brain, then hopefully it will seem like common sense to you, and be a valuable piece of knowledge as you embark on your databasing adventure.

    As your going to end up with about 5 or 6 tables, I'd also recommend getting comfortable with joins, unions and subselects in SQL. These are going to be your bread and butter for creating neat, efficient table lookups.

    Once you've got those skills down, I'd then start drawing, just on paper what I think to be a good table layout, with relationships scribbled on (complete with their primary, alternative, and foreign keys)... Once you're confident that their normalised to Boyce Code form, and that they allow access to all the data you require, with the information that will be available to you in the first instance, then I would show it to someone else, or post it on here, or whatever, just to get a second set of eyes to glance at it, and see if there's anything missing.

    Once you're confident your groundwork design is solid, then I'd start building on that baby. Start creating the tables, keys and relationships within Access. Setting up their datatypes accordingly, and using any constraints that are necessary to retain the integrity of the data.

    Again, I'd show it to some other people, get them to look at it, and confirm with me that everything is right, and everything matches my initial (correct) plan.

    Then, I'd start on the forms, and all the code that goes with it. This is where the bulk of the workload normally comes in, and definitely where you will start to notice cracks in your foundation, so to speak, if you're choice of tables and fields were poor, or not normalised.


    That's normally how I go about approaching a project like this. Sometimes, rather than writing it out on paper, I do a quick excel workbook, using different sheets to represent different tables, and using one sheet to act as a form, and try and illicit various bits of information from it. If I find that I simply can't get the information I want, or that it's incredibly difficult to retrieve certain bits of information, then I know that there's probably an issue in my tables somewhere. Excel has the benefit of being available, used frequently, so people normally have at least a basic understanding of how to use simple formulaes in it, and incredibly quick to test basic designs with the method I just mentioned.


    Ultimately, if you don't have any prior experience with databases, then to produce a detailed, robust and above all else, accurate system, you're going to have to do a little groundwork. But I think you'll find it an incredibly rewarding experience as you watch a behemoth of a system emerge over the coming weeks and months out of nothing, and you'll learn A LOT everyday, without having to sit in front of a book, reading it.


    Good luck, and remember, there's a lot of people that stalk these forums that are more than happy to help you out if you need it.
    Last edited by kez1304; 06-15-12 at 06:26.
    Looking for the perfect beer...

  4. #4
    Join Date
    Jun 2012
    Posts
    3

    Smile Can't fault the responses

    Hi Sam and Kez,

    Thanks for the replies. I didn't make it clear but I'm not completely unfamiliar with the concepts in Access, or even normalisation, although I'm sorely lacking in the practical abilities. My thinking was that I could use Access to design a small scale version of this and test the concepts, then possibly migrate the design to Oracle later on.

    Ok, I have a general question for you if you wouldn't mind giving me your opinion. Is it better to have a single, long, "thin" table or many smaller tables?
    As there are approx 500 courses, and each course has approx 500 students, would I be better creating a table for each course to monitor attendance on that course? I am pondering whether to create a table called Course1, for example, with fields of "register1, register2 ...etc" and then simply inserting the studentID into the field. The drawback is that I would need hundreds of tables that way, but each table would be relatively small.

    The alternative is to create a field for every student in table "Course1", and insert the date of attendance into each row, but that would be a very "wide" table, which feels wrong to me.

    For now, I'm solely thinking of the physical register which is taken on paper at the moment, which basically records a session time/ date, a lecturer, and a long list of students. The university itself obviously has a master table which records all the students linked to each module, so I can populate the register from that, and internally to my database I can have a field which associates the student with a given session time and day.

    Sorry, I've just realised I'm rambling. I'd appreciate your thoughts on the "many tables v few tables" question, and I hope to have a sketch of the tables I've got planned by this afternoon.

    Incidentally, I must look up how to create a foreign key in a table.

    Finally, I just wanted to share a terrible joke with you. "My sex therapist says I've got a wood fetish, as I just can't stop touching myshelf"

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by gordontheglue View Post
    Ok, I have a general question for you if you wouldn't mind giving me your opinion. Is it better to have a single, long, "thin" table or many smaller tables?
    I would STRONGLY (there aren't enough typographical tools to emphasise that enough) advise against either of those.

    By doing either of those you're totally disregarding the principle of a RELATIONAL database.

    You'd have, just off the top of my head:

    Students:
    - Student ID
    - Names
    - Addresses
    - Etc...

    Courses:
    - Course ID
    - Title
    - Credit worth
    - Etc...

    Enrolment:
    - Course ID
    - Student ID


    Everytime a student enrols on a course, the ONLY table that gets updated, is the enrolment table. It's primary key would be a combination of both 'Course ID', and 'Student ID'.


    That table would get rather large, but that's absolutely not a problem, and is the way you should be approaching this.


    Then whenever you want to find out what courses student ID '12345' is enrolled on, you'd:

    1) Search for every instance of [Student ID] '12345' within the Enrolment table.
    2) That would inform you that [Student ID] '12345' is currently studying [Course ID] 'MJ412', 'FV410' and 'JR452'.
    3) You'd then search through Courses table to find that 'MJ412' has the [Title] 'Molecular Biology 101', 'FV410' has the [Title] 'Tennis with women' and 'JR452' has the [Title] 'Cooking with Kevin'.


    You use the Enrolment table as a RELATIONAL table, that stores all the students relationships with courses.


    The example you gave with times and dates will require more complex and well thought out table design. But hopefully this makes some sense?


    Either way, please, for the love of god, don't do either of your current ideas, as they won't work, and will cause endless problems throughout development, let alone when it comes to maintaining those tables.


    PS: Nice joke.


    PPS: To expand on this a little, when designing things like this, you always want to try and model them on real life. For this example, a student is just that, a student. It's a person, and a person carries with it certain attributes (ID number, Name, Address, Sex, Phone Number, etc.), a student isn't a course though, so the representation of it shouldn't contain any information about a course, at all.

    Likewise, a course is a set of information that needs to be presented, it also needs to be organised, so it will be presented at certain times, on certain days, as well as being worth a certain amount of credits in the universities eyes. It is not however, a student, and should therefore not have any reference to a student.

    However, this being said, there is a RELATIONSHIP between the two. Thus why we have the enrolment table, to model this relationship. So student 12345 has a RELATIONSHIP with the course MJ412, but it's nothing more than that, a relationship.

    Keeping things separated like that keeps it incredibly simple to use, as it just makes sense when looking at the real world your database model is based on, as well as being simple to maintain, as there's aren't hundreds of tables, or hundreds of fields on a single table, there are small, concise tables, that fill a specific role in the real world, as well as in your database.
    Last edited by kez1304; 06-15-12 at 10:39.
    Looking for the perfect beer...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i'd agree with most of what Kez suggests
    1) Search for every instance of [Student ID] '12345' within the Enrolment table.
    2) That would inform you that [Student ID] '12345' is currently studying [Course ID] 'MJ412', 'FV410' and 'JR452'.
    3) You'd then search through Courses table to find that 'MJ412' has the [Title] 'Molecular Biology 101', 'FV410' has the [Title] 'Tennis with women' and 'JR452' has the [Title] 'Cooking with Kevin'.
    except when you search for courses a student is enrolled on, at the same time you can retrieve the name of the course from the courses table. the whole point of a relational database si that stuff is related to other stuff. providing there is a path way that connects data in able A to data in table B you can always retrieve it in one hit. even if you have to go through tables C,f, & Z. although in this case it would be straightforward enough

    Code:
    select Courses.ID, Courses.Title, Courses.Credits from Courses
    join Enrollment as Enrollment.CourseID = Courses.ID
    Where Enrollment.StudentID = blah
    of course you could also retrieve the students name and details at the same time if you so required,

    Code:
    select Courses.ID, Courses.Title, Courses.Credits, Students.Name from Courses
    join Enrollments as Enrollments.CourseID = Courses.ID
    join Students as Students.ID = Enrollemnts.StudentID
    Where Enrollment.StudentID = blah

    a couple of tips for now

    ...don't uses spaces in column or table names. decide now if you want to use CamelCase or underscores. eg:- StudentID or student_id, or ThisIsMyColumnName or this_is_my_column_name. some people obsess about whichone to use.. it deosnt' matter but what ever you do do stick to it.
    Some suggest underscores and no capitals (as soem SQL engines are case sensitivce, some aren't

    ...be aware of reserved words for whatever databases(s) you are targetting, essentially these are words you must not use as it causes the SQL enbgine to sulk, which is never a pretty sight. do a google search for reserved words for your db

    ...personal preference try to avoid using the tablename within that table eg in tablke students use ID not StudentID.. why it looks ugly
    Code:
    SELECT Students.StudentID, .....
    Code:
    SELECT Students.ID, .....
    by all means use the tablename when its used as a foreign key in another table as Kez's suggested enrolments table
    I'd suggest you name your tables in the plural of what they contain
    eg Students. EnrollmentDetails,Courses etc...

    ...bone up on normalisation, there's plenty of references out there but these two seem to have lasted the test of time
    The Relational Data Model, Normalisation and effective Database Design
    Fundamentals of Relational Database Design -- r937.com
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    except when you search for courses a student is enrolled on, at the same time you can retrieve the name of the course from the courses table. the whole point of a relational database si that stuff is related to other stuff. providing there is a path way that connects data in able A to data in table B you can always retrieve it in one hit. even if you have to go through tables C,f, & Z. although in this case it would be straightforward enough
    Yeah, I realise that. I just wanted to keep things simple, and talk him through how you go about designing a single SQL SELECT statement. I was taught to do it with alias' for each step you wanted, then compile the alias' together, and finally remove the alias' to get a complete SELECT statement.

    But Gordon, yeah, you'd pull that in with just one statement, not with several, but for educational purposes, I think that's the best way to explain it.


    Quote Originally Posted by healdem View Post
    ...don't uses spaces in column or table names. decide now if you want to use CamelCase or underscores. eg:- StudentID or student_id, or ThisIsMyColumnName or this_is_my_column_name. some people obsess about whichone to use.. it deosnt' matter but what ever you do do stick to it.
    Some suggest underscores and no capitals (as soem SQL engines are case sensitivce, some aren't
    I'm personally guilty of doing this. Although, as far as I can tell, any database engine made within the last 8-10 years supports spaces in field names, so I can't see a problem with this, unless you want to migrate to MS SQL 2000 server, for some insane reason. It just seems like omitting spaces is a relic from the past, and is no longer a necessary requirement. As a result it just makes code more difficult to read, when using [RequiredBillingMaterial] instead of [Required Billing Material], especially in long, complex queries.


    I'm a fan of formatting my SQL statements in (what I find) an easy to read manner...

    Code:
    SELECT TableName.[Field Name]
       |        |          ^
       |        |    Field name always enclosed within []'s, even if it's a single word, and therefore not required.
       |        ^
       |     Table names always present (even though not always required), and first letter capitalised.
       ^
    Always capitalise SQL commands (SELECT, FROM, WHERE, GROUP, ORDER, etc)
    But I think it's important that you find a style you're comfortable with, and stick with it. When I read other peoples code, I'm not fussed how it's written, just so long as it's consistent throughout.
    Last edited by kez1304; 06-15-12 at 11:21.
    Looking for the perfect beer...

  8. #8
    Join Date
    Jun 2012
    Posts
    3

    Whoops

    Firstly, thanks Kez and Healdem for the detailed replies. How on earth do you find the patience to deal with fresh new idiots like me on a regular basis?

    Oh dear, it seems I am once again guilty of not communicating properly. It seems like my wife may be correct after all, I never say what I mean. I know about the relational stuff, I had already created a few tables, I was specifically trying to work out how to do that one. To elucidate, I have attached a picture of the relationships window as it stands now.

    A table already exists somewhere within the university which covers enrolment, so I haven't worked too hard on that other than to give myself a table of some raw data.

    I've realised that I need to create a table called Registers (although in this pic it is called DefineRegisters) which notes the date, time, lecturer and module that the register is for.

    I've got a couple of simple lookup tables for module codes and lecturers, that I don't really need to think about.

    My problem areas come with the attendance and preferences parts. As well as recording the students who attend on a given register, I also need to note their preferred session day/ time and only bring up those students on future registers. I feel that this is a good way to do it, but I will immediately go forth and read those links on normalisation that Healdem supplied as that's the part where I'm a bit concerned I haven't understood things deeply enough.

    For the moment, if I assume that this table design was correct, it would be a simple matter to create a query to take the data from the DefineRegister form fields and populate the DefineRegister table (just playing with that idea at the moment, hence the design on the picture). Then I would need to create another query to select all the students for the relevant module who match the preferred session or who have no preferred session. That seems to be the simplest solution so far with the smallest data footprint - although it is still larger than my waistline on boxing day morning.

    That's why I was emphasising the attendance and preferences part in my question earlier, not because I didn't know about the relationships side but because I didn't want to waste your time with the absolute basics. However, there are some valuable bits of information in those postings you've given me so I will take the time now to go and look them up. It did divert me away from my earlier plan of creating dynamic tables and all sorts of other complicated stuff that would have had me pulling my arms off and beating myself over the head with the soggy ends.

    Thanks
    Gordon
    Attached Thumbnails Attached Thumbnails relationshipsAttendance.jpg  

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •