A Smart Way To Store Weekdays In Database

Optimization techniques for fast query

When we work on a specific problem, We first think of the solution for the worst-case scenario which leads to a brute force approach to solving the problem. This is OK most of the time, as we don’t get much time to think about an optimal solution due to tight deadlines. In this blog post, We’re talking about storing weekdays data model problems. First, we go through the problematic approach – brute force, then optimize our data model so that we can optimize our database tables for the fast queries.

Let’s give a perspective to our problem first, suppose we are developing an application and in our application, we need to manage branches (e.g branches of a restaurant or supermarket) and each branch has its own weekdays.

So we can design that in an RDBMS database like this, we make a table branches where we store branch-related information and for the weekdays of a specific branch we can store that in a pivot table like branch_weekdays which represents one to many relationships.

Thanks Rafat Rashid for proofreading my post

Note: We’re storing start_time and end_time in a military format which is why we define the type as int

In this design, the pivot table can store the days’ info of a branch which represents one to many relationships. For small data, it’s completely fine as it’s not going to be a problem.

But just think like this, in the worst case if every branch has 7 days as it’s a weekday in the table then the table becomes one of the major problems when there are a million rows in the branches table which makes the branch_weekdays table 7 times larger than the branches table. If we have several tables like that in our application and we need to join tables to serve some data, this will be a major problem as it will make our query slow when we need to join these tables.

Solutions:

To solve this problem, we use the binary operation to store the days as a mask in the branches table without managing a separate pivot table like below

// Contains check if a `needle` can be found in the `haystack` 
// e.g.
// haystack -> []slice
// needle -> int
// result -> bool
func Contains(haystack interface{}, needle interface{}) bool {
	switch reflect.TypeOf(haystack).Kind() {
	case reflect.Slice:
		s := reflect.ValueOf(haystack)

		for i := 0; i < s.Len(); i++ {
			if reflect.DeepEqual(needle, s.Index(i).Interface()) {
				return true
			}
		}
	}
	return false
}

// GenerateAvailabilityMask covert days slice into binary mask value
func GenerateAvailabilityMask(days []int) *int {
    mask := 0
    for i = 0; i < 7; i++ {
        if Contains(days, i) {
            mask += int(math.Pow(2, float64(i)))
        }
    }

    return &mask
}

// input
// [0,1,2,3,4,5,6]
// output
// 127

We need a function to generate weekdays from the mask as we’ll return days as a comma-separated string

// GenerateWeekdays convert mask value into weekdays comma separated string
func GenerateWeekdays(mask int) string {
	var sb strings.Builder
	nbits := 7
	for i := 0; i < nbits; i++ {
		if IsCurrentDayAvailable(mask, i) {
			sb.WriteString(strconv.Itoa(i))
			sb.WriteString(",")
		}
	}
	return TrimSuffix(sb.String(), ",")
}

// input
// 127
// output
// "0,1,2,3,4,5,6"

// TrimSuffix remove suffix from the string if string ends with the suffix
func TrimSuffix(s, suffix string) string {
	if ok := strings.HasSuffix(s, suffix); ok {
		s = s[:len(s)-len(suffix)]
	}
	return s
}

// IsCurrentDayAvailable check if current weekday is available in availability mask
func IsCurrentDayAvailable(mask int, currentDay int) bool {
	x := 1
	return (mask & (x << currentDay)) > 0
}

IsCurrentDayAvailable function is where we apply the bitwise AND (&) operation to find if the current day is available in the mask or not.
To get the current day we can use the below function

// CurrentLocalTimeAndWeekday use timezone to get current zone time and weekday and return current weekday & current time in military format 
func CurrentLocalTimeAndWeekday(tz string) (int, int) {
	militaryLayout := "1504"
	loc, err := time.LoadLocation(tz)
	if err != nil {
		logrus.Error("error occurred when trying to find timezone location.", err)
		return 0, -1
	}
	t := time.Now().In(loc)
	currentWeekday := int(time.Now().In(loc).Weekday())

	militaryTime, err := strconv.Atoi(t.Format(militaryLayout))
	if err != nil {
		logrus.Error("error occurred when converting time to military integer format.")
		return 0, -1
	}
	return militaryTime, currentWeekday
}

In Conclusion, We may not often get enough time to think or come up with an optimized solution in the first go but if we put some thought into the problem, we may come up with solutions that make the database query faster as we reach to million rows.