Should I use Inner Join in this case?

Question

I am working on a student attendance mini-project, and I don't know how to proceed for my database. I'm new to SQL and databases in general so this might seem dumb to you.

So, I want to do a database containing the table student, which contains : student_id (primary key) , name (string) and attendance(boolean) (that's the bare minimum, i'll add more afterwards) and I want to register the daily attendance of the students. So I want to have all the students tied to every date of the week.

I created a date table in phpMyadmin but I don't know how to proceed to link them, i've tried an Inner Join and it was successful.

The problem is : If i want to add another line to the student table my table won't update, so is there a way to "automatically" tie all the students to the date table ?

Sorry if this seems confused I've tried my best to summarize it !

You don't really need a date table. You need a student table, which stores the id and name of the student, and an attendance table, which stores the id of the student and the date(s) they attended. — Strawberry, May 30 '19 at 09:16
Possible duplicate of [How to create relationships in MySQL](https://stackoverflow.com/questions/260441/how-to-create-relationships-in-mysql) — ThisaruG, May 30 '19 at 09:16
I've thought about using FOREIGN KEY but I'm not sure if it will tie the dates to my table and more importantly how would the tables stucture will look like — Majid Benhenneda, May 30 '19 at 09:18
Have just two tables, firstly a student table with student_id as primary key and secondly a attendance table with student_id as foreign key along with the date field — MJoy, May 30 '19 at 09:18
student_id|attendance_date - alternatively, you store the dates they were absent, if that makes more sense. — Strawberry, May 30 '19 at 09:22
Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. Explain 'add another line to the student table' & '"automatically" tie all the students to the date table'. (Putting words in scare quotes does not make clear the idiosyncratic specific meaning that you didn't write out.) — philipxy, May 31 '19 at 21:33
Time to read a published academic textbook on information modelling, the relational model & DB design. (Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design.) Dozens of published academic information modeling & database design textbooks are online free in pdf. stanford.edu has a free online course. PS *Tables* represent relation(ship)s/associations. [Foreign keys are not needed to join tables!](https://stackoverflow.com/a/23842061/3404097) PS — philipxy, May 31 '19 at 21:34
Possible duplicate of [How do you deal with m..n relationships in a relational database?](https://stackoverflow.com/questions/183742/how-do-you-deal-with-m-n-relationships-in-a-relational-database) — philipxy, May 31 '19 at 21:41

score 0 · Accepted Answer · answered May 30 '19 at 12:20

Lets have some idea about tables should be there to implement a proper Student Attendance system in place. I have copied create script for some of my tables that used for maintaining Students record per course. I hope following sample Table scripts with relation will help you understanding regarding table structure and also to solve your issue.

Please be noted, That this table structures for your your reference only. You can add/remove tables/columns as per your requirement once you get an overall idea from this post.

CREATE TABLE `staff` (
  `id` int(11) NOT NULL,
  `type` varchar(200) DEFAULT NULL,
  `first_name` varchar(200) DEFAULT NULL,
  `last_name` varchar(200) DEFAULT NULL,
  `emal` varchar(200) DEFAULT NULL,
  `contact` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `batch` (
  `id` int(11) NOT NULL,
  `department` varchar(200) DEFAULT NULL,
  `details` varchar(200) DEFAULT NULL,
  `staff_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_staff_idx` (`staff_id`),
  CONSTRAINT `batch_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `batch_id` int(11) DEFAULT NULL,
  `first_name` varchar(200) DEFAULT NULL,
  `last_name` varchar(200) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `contact` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_student_idx` (`batch_id`),
  CONSTRAINT `batch_student` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `details` varchar(200) DEFAULT NULL,
  `staff_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `course_staff_idx` (`staff_id`),
  CONSTRAINT `course_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attendence` (
  `course_id` int(11) NOT NULL,
  `student_id` int(11) DEFAULT NULL,
  `class_date` date DEFAULT NULL,
  KEY `att_course_idx` (`course_id`),
  KEY `att_student_idx` (`student_id`),
  CONSTRAINT `att_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `att_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Your required data will finally store into table Attendance. From this table data, you will be able to find list of students absent/present per date and per course. Remember, the attendance table should enrich daily from a automated OR a manual process.

Should I use Inner Join in this case?

1 Answers1