2

I'm trying to make a attendance management system for my college project. I'm planning to createaone table for each month. Each table will have 

OCT(Roll_no int ,Name varchar, (dates...) bool)

Here dates will be from 1 to 30 and store boolean for present or absent. Is this a good way to do it? Is there a way to dynamically add a column for each day when the data was filled. Also, how can I populate data according to current day.

Edit : I'm planning to make a UI which will have only two options (Present, absent) corresponding to each fetched roll no. So, roll nos. and names are already going to be in the table. I'll just add status (present or absent) corresponding to each row in table for each date.

Affan
  • 25
  • 6
  • 2
    This is not a good way to do this. You should have one row in a table per *date*, not per *month*. – Gordon Linoff Oct 19 '17 at 16:08
  • Which SQL are you using? Can you post a full code? Refer to https://stackoverflow.com/help/how-to-ask for tips on how to make questions better. – Moseleyi Oct 19 '17 at 16:12
  • which database? – Buddhi Oct 19 '17 at 16:17
  • I'm using Postgress sql – Affan Oct 19 '17 at 16:35
  • Why are you even using different tables per months? Do you expect a large number of rows in this tables? And you are aware that this makes reports and cross-months queries utterly complicated? Your month is already in the date - if you need to extract the month name, SQL can do that for you. – A. Scherbaum Oct 21 '17 at 15:36
  • @A.Scherbaum I was expecting around 200 rows per table. And there will be no cross-month operations. Can you elaborate by what do you mean by your month is already in date? – Affan Oct 21 '17 at 19:25
  • 200 rows is at the very low end, you blow your catalog up with hundreds or thousands of tables. And just because you think that there will be no cross-months operations doesn't mean that there will ne none in the feature. What if someone wants a report about a student for the entire time? How do you deal with that? – A. Scherbaum Oct 21 '17 at 20:37
  • 1
    Regarding the month: if you store a date in your table, this date already contains a months (plus a year, plus a day). Utilize that, instead of creating too many tables. – A. Scherbaum Oct 21 '17 at 20:38
  • @A.Scherbaum Makes sense. Thanks. I actually want to print reports too and it will be in the format mentioned in the question. So, I thought it would be easier if I create table in the same format. – Affan Oct 22 '17 at 04:07

2 Answers2

0

I would use Firebase. Make a node with a list of users. Then inside the uses make a attendance node with time-stamps for attended days. That way it's easier to parse. You also would leave room for the ability to bind data from other tables to users as well as the ability to add additional properties to each user.

Or do the SQL equivalent which would be make a table list of users (names and user properties) with associated keys (Primary keys in the user table with Foreign keys in the attendance table) that contained an attendance column that would hold an array of time-stamps representing attended days.

Either way, your UI would then only have to process timestamps and be able to parse through them with dates.

Though maybe add additional columns as years go so it wouldnt be so much of a bulk download.

Edit: In your case you'd want the SQL columns to be by month letting you select whichever month you'd like. For your UI, on injecting new attendance you'd simply add a column to the table if it does not already exist and then continue with the submission. On search/view you'd handle null results (say there were 2 months where no one attended at all. You'd catch any exceptions and continue with your display.)

Ex:

User

Primary Key - Name

1 - Joe

2 - Don

3 - Rob

Attendance

Foreign Key - Dates Array (Oct 2017)

1 - 1508198400, 1508284800, 1508371200

2 - 1508284800

3 - 1508198400, 1508371200

sfxworks
  • 1,031
  • 8
  • 27
  • So, are you suggesting the attendance column will have roll_no as foreign key and corresponding to it, there will be timestamps when the student of that roll number was present? If yes, how will I fetch monthly record? – Affan Oct 19 '17 at 16:44
  • Yes. And ah you wanted it by month. That's right. So instead of the Attendance Table being Foreign Key - Year2017 - Year2018 and so on you'd do Foreign Key - Month1Year2017, Month2Year2017 and so on. Then just select the key and month column you want to get the attendance for that month. I edited my answer to reflect this, – sfxworks Oct 19 '17 at 16:59
  • The attendance table will have (roll_no foreign key, oct_2017, nov_2017 .... ) ? How to add data into it with the UI I've mentioned in the question? – Affan Oct 19 '17 at 17:22
  • Thanks, Now I understand and this will work. What will be the query for inserting timestamp only into the array column and in the end of array? – Affan Oct 19 '17 at 17:54
  • Not sure if postgress has this but you'd use something similar to CONTACT() https://stackoverflow.com/a/3765635/5488621 . If it doesn't you could always download the selection first and then send it back with the new data. – sfxworks Oct 19 '17 at 18:02
  • It works with postgress but not sure if it will work with array because we're adding a new element instead of adding something to existing element. – Affan Oct 19 '17 at 18:13
  • You'd just use a string instead and then treat it as an array in the application. – sfxworks Oct 19 '17 at 18:31
  • `1508198400` is not a date –  Oct 19 '17 at 18:53
  • @a_horse_with_no_name it is an epoch timestamp. – sfxworks Oct 19 '17 at 19:05
0

I'd agree with Gordon. This is not a good way to store the data. (It might be a good way to present it). If you have a table with the following columns, you will be able to store the data you want:

role_no (int) Name (varchar) Date (Date) Present (bool)

If you want to then pull out the data for a particular month, you could just add this into your WHERE clause:

WHERE DATEPART(mm, [Date]) = 10 -- for October, or pass in a parameter

Dynamically adding columns is going to be a pain in the neck and is also quite messy

Gez Jones
  • 214
  • 1
  • 3
  • For each date, there will be all roll numbers? Eg: Date 1 will be corresponding all roll nos. Then date 2 corresponding all roll nos. – Affan Oct 19 '17 at 16:49