Simple database table design / layout
I'm just wondering, as an example of a hypothetical , that it would be the best way to layout the table for the following scenarios:
Let's say I am writing an application to track student attendance . At the beginning of each year, I want to add all the students (will I do it manually - now if each student is assigned a student ID? Let's call this table to Students). Now, every day, I am going to display all students in Table Students and allow the user to select attendance.
So how should I lay my table? (If you don't understand what I mean, I mean what data should be entered in each column, row ...) For example, maybe there is a table "Students" with student IDs and each student creates a new row in the Attendance Table with column 1: student ID, column 2: date, column 3: status (present / not present). However, this is not very efficient. What do you think?
UPDATE: From all of these first answers, it seems that one student is on each row of the attendance table (where he / she is denoted as present / absent), but what if I were to include more than one student ID in a row, say for all students missing some day? Will it be better or worse (perhaps ambiguous)? In fact, I'm starting to think that the efficiency will be diminished because the only actions this step even helps can be easily performed already. Hmm ...
source to share
STUDENTS
table
-
STUDENT_ID
, pk -
FIRST_NAME
-
LAST_NAME
STUDENT_ATTENDANCE
table
-
STUDENT_ID
, pk, fk -
ABSENT_DATE
, pk
No need for a column IS_ABSENT
- the presence of a date indicates both, that the student is absent, and on what date. There will likely be fewer days than visits, so just keep missing dates.
Creating a primary key to combine the two columns will ensure that you don't have duplicates.
What if I have to include more than one student ID on a string, say for all students who are absent on a given day? Will it be better or worse
Then you either store the additional student_ids as a comma-separated list in one column, or additional columns for each additional student_id. Additional columns for each student will never work - you add a column for each new student every year. Concatenating the student_ids list is more realistic, but it will be a pain to pull the details if you want to report on a specific student or group of students. Due to character constraints, it runs the risk of not being able to store every student_id that may be missing in one column.
I recommend using the table STUDENT_ATTENDANCE
I suggested.
source to share
The key to designing a database is ensuring the integrity of the model. So in your example, you won't want to write down student passes for dates that fall on weekends, holidays, or nested days. Thus, you also need the CALENDAR table. STUDENT_ABSENCE will be the intersection table between STUDENT and CALENDAR. That is, it will have foreign keys for both the ID in the STUDENT table and the DAY in the calendar.
This may seem like overkill, but almost everything that happens in school is planning, so the CALENDAR is essential. You can use it as much as possible to create the best model you can.
Also consider what other attributes the STUDENT_ABSENCE table requires. At the top of my head, you can write down whether the notice was notified in advance (for example, for family vacations during the term), whether the absence was approved, whether the absence was due to illness.
source to share
I will have a MissedClasses table with StudentID as foreign key, date, rate and possibly period, and maybe another column to apologize or not. Post a record if they don't attend.
My reasoning. Hopefully most of them will participate in most of the classes, so you only need to keep track of misses.
source to share
If you concatenate multiple values into a list of values and store them in one cell, your table is no longer in first normal form, as originally defined by Codd. You can conform to the first normal form overridden by date by storing the table inside the table. Most newbies don't. They usually preempt the list of values into a comma separator and keep the entire list as if it were a single atomic value.
They later find that they can no longer use the power of relational operators, especially union, to express complex operations in an easy way. This usually costs the newbie more than "inefficiency". Even if you put the table inside the table, you will find that doing normal things with the data is much more difficult than it should be.
Most of the good suggestions you get are about decomposing tables to get a normalized schema. This is usually the best plan for you, until you learn at a later time when you break the normalization rules and when to follow them.
This is not written for the typical 12 year old. You don't look like your typical 12 year old. So I'm trying to give you a head start on the basics of good database design, rather than letting you learn bad database design in high school and then having to wean it and start over later.
source to share
I would have a table of students with student IDs and information specific to each student like name, class, etc. Then enter student_attendance table with student id, date, status (present / not present).
While it collects a lot of data, it actually doesn't have much data and it allows you to easily run several kinds of traffic reports.
You wouldn't want to put more than one student ID on a single row, because although you would have fewer rows, you would have the same amount of data and table / reporting queries would be awkward.
source to share