CODEWITHSUNDEEP
sqldatabase-design
Yashwanth
Yashwanth

Reputation: 3

Creating a table having a group of attributes with same name

I am actually trying to create a student database and I need my table to be something of this type

a picture of what i am expecting

In the above picture the date is something like a super-column name to columns-A,B,C,D which are subject names. My data will be inserted in those A,B,C,D columns.

My final aim is to access attendance in all subjects based on the date.

Something like:-

       select 27-01-2020 from 'table-name';

the above query should give me the attendance in all subjects on 27-01-2020 date

Is there any way to create such a table or similar to this one?

Upvotes: 0

Views: 725

Answers (1)

lsabi
lsabi

Reputation: 4456

Assuming you have a table for the students and a table for the subjects, you could use a third table, representing the attendance of the students to the different subjects.

Such table will have a date, a reference to the subject, a reference to the student and an attendance column, possibly of type boolean, denoting if the student was present during that day at that subject. You may also add a slot, in case there are more than one hour of lecture for a subject on that day (but this is up to you and your needs).

There should also be a uniqueness constraint on the tuple (date, student, subject), so that there cannot be a student present at two different lectures the same day (unless you use the slot, to which the uniqueness constraint should also span).

Upvotes: 1

Related Questions