CODEWITHSUNDEEP
mysqldatabasetypes
Peka Chawngthu
Peka Chawngthu

Reputation: 77

What is the best data type for storing a string of 2014-2015?

In my student database, I want to store school session which begins, for instance, April of every year and ends in March of the year following. And I want to store the session year as 2014-2015, 2015-2016 and so on. I am not sure which data type is most suitable for storing such string. Please help and suggest.

Upvotes: 1

Views: 126

Answers (2)

Dan J
Dan J

Reputation: 16708

What is the best data type for storing a string of 2014-2015?

It depends what you want to do with it. So to provide an answer, we've got to make some assumptions about that.

If you're only storing the string "2014-2015" to identify the session to a human viewer, then you can simply treat "2014-2015" as an arbitrary label for the session, no different from calling the session "Session 3", or "Frank". So saving it as a VARCHAR with an appropriate length should suffice.

If, however, you intend your system to have logic related to the date range represented by "2014-2015", i.e. to figure out if the "2014-2015" session comes before, after, or overlaps the "2015-2016" session, saving this as string data is suddenly a very bad idea.

Since it's reasonable that you might want to satisfy both of those requirements, I recommend separating the data: have a string field that identifies the session as "2014-2015" and have two DATE fields that identify the start and end dates of the session. This lets you use the correct data type in the correct context, without forcing you to write a lot of logic to convert between types as needed.

Upvotes: 2

Rick James
Rick James

Reputation: 142208

For hundreds of rows, simply CHAR(9) CHARACTER SET ascii.

For millions of rows, consider these:

  • ENUM('2014-2015', '2015-2016', ...) -- 1 byte
  • TINYINT UNSIGNED (1 byte) and JOIN to a table with the strings in them
  • YEAR (2 bytes), but then have the SELECTs 'construct' '2014-2015' from '2015'.

Upvotes: 1

Related Questions