title: Multiple columns, each with a list of foreign keys from the same table
body: <p>I have a database with three tables where I am trying to define a weekly schedule for each <code>client</code>. I'd like to be able to define something like this:</p>
  <p>Client: Bob's Burgers<br />
  Weekly Hours:</p>
  <ul>
  <li>Monday (8am-10pm)</li>
  <li>Tuesday (8am-10pm)</li>
  <li>Wednesady (8am-10pm)</li>
  <li>Thursday (NONE)</li>
  <li>Friday (8am-6pm, 8pm-11pm)</li>
  <li>Tuesday (8am-6pm, 8pm-11pm)</li>
  </ul>
  <p>I'm thinking that I'll need tables for <code>clients</code>, <code>weekly hours</code>, and <code>daily hours</code>. The starting points for each of the classes I am mapping them to in SQLAlchemy are below, but I'm having trouble wrapping my head around if this is the best way to structure the data. I am <strong>not</strong> tied to this data structure, but I <strong>am</strong> tied to storing the individual shift start/end times as <code>Time</code> columns so I can sort/filter/etc.</p>
  <pre class="lang-py prettyprint-override"><code>class Client(Base):
      __tablename__ = &quot;clients&quot;
  
      id = Column(postgresql.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
      # Other attributes will go here...
      name = Column(String, unique=True, index=True)
      # NEED TO REFERENCE THE CLIENT'S WEEKLY HOURS HERE
  
  
  class DailyHours(Base):
      __tablename__ = &quot;daily_patrol_hours&quot;
  
      id = Column(postgresql.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
      patrol_start = Column(Time, index=True)
      patrol_end = Column(Time, index=True)
      # EACH DAILY HOURS RECORD SHOULD CONNECT TO MULTIPLE WEEKLY HOURS RECORDS
  
  
  class WeeklyHours(Base):
      __tablename__ = &quot;weekly_patrol_hours&quot;
  
      id = Column(postgresql.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
      client_id = Column(postgresql.UUID(as_uuid=True), ForeignKey(&quot;clients.id&quot;), index=True)
      monday_hours = # List of none, one, or more DailyHours records
      tuesday_hours = # List of none, one, or more DailyHours records
      wednesday_hours = # List of none, one, or more DailyHours records
      thursday_hours = # List of none, one, or more DailyHours records
      friday_hours = # List of none, one, or more DailyHours records
      saturday_hours = # List of none, one, or more DailyHours records
      sunday_hours = # List of none, one, or more DailyHours records
  
      # NEED A RELATIONSHIP TO CONNECT BACK TO CLIENT
  
  </code></pre>
  <p>In the end, I'm looking for the cleanest way to see a client's schedule, and - when presented with a log record from a visit to the client, determine whether the log fell within the predetermined patrol hours for that client or not.</p>
  <p>Thanks!</p>