CODEWITHSUNDEEP
databasedatabase-design
Logan Serman
Logan Serman

Reputation: 29870

In a database, is it better to store a time period as a start/end date, or a start date and a length of time?

This is a completely hypothetical question: let's say I have a database where I need to store memberships for a user, which can last for a specific amount of time (1 month, 3 months, 6 months, 1 year, etc).

Is it better to have a table Memberships that has fields (each date being stored as a unix timestamp):

user_id INT, start_date INT, end_date INT

or to store it as:

user_id INT, start_date INT, length INT

Either way, you can query for users with an active membership (for example). For the latter situation, arithmetic would need to be performed every time that query is ran, while the former situation only requires the end date to be calculated once (on insertion). From this point of view, it seems like the former design is better - but are there any drawbacks to it? Are there any common problems that can be avoided by storing the length instead, that cannot be avoided by storing the date?

Also, are unix timestamps the way to go when storing time/date data, or is something like DATETIME preferred? I have run into problems with both datatypes (excessive conversions) but usually settle on unix timestamps. If something like DATETIME is preferred, how does this change the answer to my previous design question?

Upvotes: 11

Views: 5057

Answers (6)

Lars Rönnbäck
Lars Rönnbäck

Reputation: 873

If the membership may toggle over time I would suggest this option: 

user_id INT, 
since_date DATE, 
active_membership BIT

where the active_membership state is what is toggled over time, and the since_date is keeping track of when this happened. Furthermore, if you have finite set of allowed membership lengths and need to keep track of which length a certain user has picked, this can be extended to:

user_id INT, 
since_date DATE, 
active_membership BIT, 
length_id INT

where length_id would refer to a lookup table of available and allowed membership lengths. However, please note, that in this case since_date becomes ambiguous if it possible to change the length of your membership. In that case you would have to extend this even further:

user_id INT, 
active_membership_since_date DATE, 
active_membership BIT, 
length_since_date DATE,
length_id INT

With this approach it is easy to see that normalization breaks down when the two dates change asynchronously. In order to keep this normalized you actually need 6NF. If your requirements are going in this direction I would suggest looking at Anchor modeling.

Upvotes: 0

Petro Semeniuk
Petro Semeniuk

Reputation: 7038

It really depends on what type of queries you'll be running against your date. If queries involve search by start/end time or range of dates then start/and date then definitely go with first option.

If you more interested in statistic (What is average membership period? How many people are members for more than one year?) then I'd chose 2nd option.

Regarding excessive conversion - on which language are you programming? Java/Ruby use Joda Time under hood and it simplifies date/time related logic a lot.

Upvotes: 2

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

If depends on whether you want to index the end date, which in turn depends on how you want to query the data.

If you do, and if your DBMS doesn't support function-based indexes or indexes on calculated columns, then your only recourse is to have a physical end_date so you can index it directly.

Other than that, I don't see much of a difference.

BTW, use the native date type your DBMS provides, not int. First, you'll achieve some measure of type safety (so you'll get an error if you try to read/write an int where date is expected), prevent you from crating a mismatching referential integrity (although FKs on dates are rare), it could handle time zones (depending on DBMS), DBMS will typically provide you with the functions for extracting date components etc...

Upvotes: 3

Quinn Wilson
Quinn Wilson

Reputation: 8233

The two strategies are functionally equivalent, pick your favorite.

Upvotes: 1

Sachin Kainth
Sachin Kainth

Reputation: 46750

I would disagree. I would have a start and end date - save on performing calculations every time.

Upvotes: 2

HJW
HJW

Reputation: 23443

From a design point of view, i find it a better design to have a start date and the length of the membership.

End date is a derivative of the membership start date + duration. This is how i think of it.

Upvotes: 2

Related Questions