Question: Is it possible to take column names for sqlite database as input from user in an android app?

Question

Is it possible to take column names for sqlite database as input from user in an android app?

Answers 1
Added at 2017-04-25 03:04
Tags
Question

I am creating an android app for my college faculties through which they will be able to keep and maintain the attendance of students in their lectures. I thought of designing the database this way

Date | Student1 | Student2 | . . . . . . |. . .|. . . | Student60

In this structure each INSERT INTO statement will take 61 values, one for the date and rest for presence/absence record of 60 students. But in this case the column headers have to be named by the user(the column header should be unique identifier for that particular student, like his roll no.). Is it possible? or am I completely on the wrong track? Please suggest if there is a better database design alternative. I also need to provide the users the ability to retrieve aggregate attendance % of a student. Apologies in advance if I've asked something very basic or stupid.

Answers to

Is it possible to take column names for sqlite database as input from user in an android app?

nr: #1 dodano: 2017-04-25 03:04

This is on the wrong track. Instead, make the student's ID a primary key column, and use the other columns for storing student metadata, something like this:

Students

ID | first_name | last_name |
1  | Jon        | Skeet     |
2  | Gordon     | Linoff    |
...

Attendance

ID | SID | date       | status
1  | 1   | 2017-05-24 | absent
2  | 1   | 2017-05-25 | present
3  | 2   | 2017-05-24 | present
4  | 2   | 2017-05-25 | present

Now if you wanted to find out which students were present on a given day you could use the following query:

SELECT
    s.first_name,
    s.last_name
FROM Students s
INNER JOIN Attendance a
    ON s.ID = s.SID
WHERE a.status = 'present' AND
      a.date = '2017-05-24'

Note that in practice you might use an integer (0 or 1) to store the attendance.

Towards answering your actual question, if you wanted a summary by student along with his attendance record in percent over the most recent 90 days, you could use this:

SELECT SID, 100*(SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) / 90) AS p_attedance
FROM Attendance
WHERE date > date('now', '-90 days');
GROUP BY SID
Source Show
◀ Wstecz