Geek asks and answers

Is it better to store a time period in the database as a start and end date, start date and duration?

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

Is it better to have a table Membershipsthat has fields (each date is stored as a unix timestamp):

user_id INT, start_date INT,end_date INT

or save it as:

user_id INT, start_date INT,length INT

In any case, you can request users with active membership (for example). For the latter situation, arithmetic should be performed every time a query is executed, while in the previous situation, only the calculation of the end date (when inserting) is required. From this point of view, it seems that the previous design is better - but are there any flaws? Are there any common problems that can be avoided by keeping the length instead, which cannot be avoided by keeping the date?

Also, are unix timestamps a way to jump when storing time / date data or something like DATETIME? I am having problems with both data types (excessive conversions), but they are usually set at unix timestamps. If something like DATETIME is preferred, how will this change the answer to my previous design question?

+5
6

, . / , / , .

( ? ?), .

- ? Java/Ruby Joda Time , .

+2

, , , , , .

, , end_date, .

, .

BTW, , , int. -, ( , / int, ), ( FK ), ( ), ..

+2

. - .

+1

, .

+1

, , .

- + . .

0

, :

user_id INT, 
since_date DATE, 
active_membership BIT

active_membership - , , since_date , . , , - , :

user_id INT, 
since_date DATE, 
active_membership BIT, 
length_id INT

length_id . , since_date , . :

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 when two dates change asynchronously. For this to normalize, you really need 6NF. If your requirements go in that direction, I would suggest looking at Anchor modeling .

0
source

More articles:


All Articles