Recommended database design for relationship between business hour and it's staff and their hours

Question

Been going back and forth on how to architect this. I'm using rails,and started with Single-Table-Inheritance, then changed my mind, now I'm not sure.

BusinessHours business_id, day_of_week, start_time, stop_time

StaffHours staff_id, day_of_week, start_time, stop_time

Obviously a staff member belongs to a business, however the tables for their hours are identical.

Should I store those in one table and add a field like 'keeper_id' which stores the id of the staff or business, and a 'type' field that store 'staff_hours' or 'business_hours' to differentiate the two (a staff member and a business may have the same id, so I need to differentiate)

But then I feel like I'm almost back to STI??

Thoughts?

Answer 1

You should keep similar data together, however, just because the columns appear the same doesn't mean the data is similar. I'd say you're modeling two different things, so use two tables. Plus then you're allowing for the tables to diverge over time as you add columns that the two sets of hours have in common, like time off, or lunch hours, or minimum staff requirements or manager requirements, or...

Answer 2

To me it seems like you are modeling something (staff, business, whatever) and its hours.

So I would have an a business, staff and hours model. With the hour model then having a polymorphic relationship to business, staff and anything else you may need it to:

http://guides.rubyonrails.org/association_basics.html#polymorphic-associations

The hour model would have hourable_id and hourable_type fields to point to a business or a staff record.

Answer 3

Much depends on your application domain. I don't think we have enough information here to know exactly what the correct approach is in your situation. For instance, if your application is primarily concerned with managing and reporting on employee and business schedules/hours, and reporting on those schedules, it might make sense to have a single 'hours' table, and relate to it polymorphically. This might look like:

class Business < ActiveRecord::Base
  has_many :hours, :as => :scheduleable
end

class Staff < ActiveRecord::Base
  has_many :hours, :as => :scheduleable
end

class Hour < ActiveRecord::Base
  belongs_to :scheduleable, :polymorphic => true
end

Here you can still use STI to fork the different Hour functionality if need be. (Note: use the singular when naming your table model, i.e., BusinessHour, not BusinessHours):

class BuisnessHour < Hour
  validates :some_business_hour_rule
end

Etc. The key question with STI is, given what I know about my domain, is it likely I would add many attributes/columns specific to a subclass, such that I would have many null columns in, for instance, a row in the hours table representing a StaffHour object that doesn't use many of the BusinessHour-specific attributes/columns?

If so, then maybe STI doesn't make sense for you. But if from what you know about your application's concerns, the primary difference between subclasses is programmatic and not data-centric, where there are different validations and business rules to operate on mostly identical data, then STI might be the right solution.

I'd sit down with the Product owner (whoever that is) and the other engineers on the team (if there are any) and whiteboard it. If you're a team of one, whiteboard it anyway and think through what your application will do with these models in the future. If you're familiar with UML, it can be a very useful tool to help you visualize the data model in a form other than code. If not, this might be a good excuse to learn a bit. Decisions like these can have a huge impact on general code stability down the road.

EDIT:

Another question to ask yourself is, does a Business-specific rule really belong on an Hour object? It could be that it's more appropriate to have such operations on the Business model itself, and keep Hour simple, unless you truly can imagine a business or staff-specific operation you would call on the Hour model itself. For example this:

class Business < ActiveRecord::Base
  def calculate_monthly_cost
    # operates on hours 
  end
end

makes more sense to me from an OOP/SRP standpoint than this:

class BusinessHour < Hour
  def calculate_business_cost
    # operates on business or uses business-specific rule 
  end
end

At least, those are the kinds of questions you need to ask to determine the right approach.

Answer 4

I think of it more like this:

A table defining Businesses:

create table Businesses
   (
   Business_Id   integer,
   Business_Name varchar(100),
   etc...
   )

A table defining Employees:

create table Employees
   (
   Business_Id   integer,
   Employee_Id   integer,
   Employee_Name varchar(100),
   etc...
   )

A table defining the type of work shifts the businesses will use:

create table Shifts
   (
   Business_Id   integer,
   Shift_Id      integer,
   Shift_Name    varchar(100),
   StartTime     datetime, -- Just the time of day the shift starts
   Duration      datetime,  -- Duration of the shift
   etc ...
   )

Then a table defining the Schedule:

create table Schedule
   (
   Business_Id   integer,
   Shift_Id      integer,
   Employee_id   integer,
   WorkDate      datetime, -- just the date, time comes from Shift table
   etc ...
   )

Then your query to display the work calendar for everyone would be:

select
   b.Business_Name,
   e.Employee_Name,
   s.WorkDate + sh.StartTime                as ShiftStart,
   s.WorkDate + sh.StartTime + sh.Duration  as ShiftEnd,
   etc ...
from
   Businesses b

   JOIN Schedule s
      on ( s.Business_Id = b.Business_Id )

   JOIN Employees e
      on ( e.Business_Id = s.Business_Id
           and e.Employee_id = s.Employee_Id )

   JOIN Shifts sh
      on ( sh.Business_Id = s.Business_Id 
           and sh.Shift_Id = s.Shift_Id )

Then another table to track actual clock-in / clock-out times.

Something like that ...