How to store integer array in mysql database [closed]

Best practice mandates that you should create a separate Attendence table with a foreign key to your Classroom table. Attendence table would have a number for the students attended that classroom that day as well as a date to indicate that particular day.

I realize that perhaps you were hoping for a solution that would inline this information into the same table, but that is not a good idea because it quickly becomes impractical to search in such data.

• For example, if you had a comma-separated list, and you wanted to see what the average attendence for all classes that falls on St. Patrick's day, you'd have to pull all attendence data and grab the nth term. Not efficient at all.
• Rather, if you had 365 columns, aside from wasting space, how would you find all attendence days whose attendence is 0 with this system? Your query would literally require 365 where conditions (it is enough to make a full grown programmer cry).

Aside for these reasons, consider that unlike the 365 column system, you're only saving the information you have available, not all information for a year's time regardless of whether or not it is being used.

In short, don't do this!

Now I realize what you might be thinking: "If I only have attendence records for the days in which class has been in session, how am I going to represent a calendar for class days set in the future?" This is a classic model/view problem. Your model represents what you need to know. Your view doesn't have to (and usually doesn't) reflect the model data. However, if you think you might need to indicate when class will be canceled and you can't represent this with the current model, then you can change the model, but not before (I would add a "state" column in the Attendence table indicating if it was canceled that day or if it was postponed.. whatever).

It is a good programming exercise to learn how to write a model that only holds the data that you need to know and build a view that can represent that information in more verbose ways. However, if the above two paragraphs don't apply to you, then please disregard.

I hope this helps.

Since the question is lacking a lot of detail we probably can't work out exactly what you need, but here's a few ideas off the top of my head:

• You could use a string like "PPPPAPPPPPP" where you pick one letter to mean present and another to mean absent. This makes it easy to add at least a few more "attendance statuses" when you inevitably need them.

• I would hope the data is meant to be sparse; i.e. we expect most students to be present most of the time, so we only really need to store information about those days where someone was not present. In that case, you can have a database of "student, day, status" triples with one row for every non-present status.

In general, even if your database supports some sort of integer array type, I would avoid using it in favor of solutions like the above, because you'll never be able to write an SQL query like "how many days was this student absent?" without adding a bunch of array iteration logic which I expect would be nightmarishly frustrating to deal with in a language like SQL. That's just not how databases are designed to be used.

SQL databases are very good at handling a very large number of rows. I would go with 1 column because databases are optimized for this. The alternative could easily result in a lot of repetetive code and make it hard to use good abstractions over your database(ORM). Just imagine looping through the 12 columns. Not possible without reflection in a statically typed language. All in all there is no downside to the one column design.