CODEWITHSUNDEEP
sql
ChrisS
ChrisS

Reputation: 734

Storing datetime or season or quarter in a relational database

I must save an event in a relational database. This event has a time when it starts. This will be precisely one of:

What would be a good way to store this in a database, so i can distinguish the three types.

Should i create a col for datetype and three other cols for datetime, quarter, season? And what would you use for season and quarter.

Upvotes: 0

Views: 364

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5518

Yes, your suggestion makes perfect sense. Create a column for datetype and three other cols for datetime, quarter, season. There are plenty of different ways to do this, here's one approach;

DateType char(1) not null, D = datetime, Q = quarter, S = season
DateTime datetime null
Quarter int null, valid values 1 to 4
Season char(2), valid values Wi, Sp, Su, Au

I would use column constraints to enforce the valid values per column, then a table constraint to enforce the rule that if DateType = D then DateTime must not be null and Quarter and Season must be null etc.

You could skip the Quarter and Season columns and use the DateTime column to store a value to represent quarters 1 to 4 or the seasons but this sort of approach almost always leads to mistakes later on. These values are sometimes called 'magic values' because they aren't what they seem, for example, does 2015-01-01 mean 1st Jan 2015 or 'Quarter 1'? When someone queries your table and forgets to look at the DateType column how will they know? I like to see schemas and data that describe themselves. With my suggestion above (or any similar approach) it would be hard to misinterpret the data in the table.

Saving a few bytes of storage or a few millionths of a second in processing are very rarely worth it - you should design something that will always work all of the time, not something that will work a little quicker, most of the time.

Upvotes: 1

Related Questions