How to model time-limited connections

I am creating my objects and fields and relating them together. I would like to connect two objects together but add the date range that they are connected to the connection. For example, I have person and role objects and a person takes on a role by creating a connection between those objects. (The connection is many-to-many.) However, the connection only lasts a certain time and I want to record that time in the connection, itself. Think of a person and committee. The person could be connected to the role “Member of XYZ committee” but only from March to September of a certain year. How can I reflect that connection for only that time period?

You may be thinking about it the wrong way. Instead of “timing” connections, use date fields and rules to control your scenario.

Hi @Greymont

This sounds like a good case for creating a new object (i.e. table).

A rule of thumb is—if you find yourself wanting to capture additional information about a field, then you usually need an object (i.e. table).

School Example
Let’s say you have a database of Students enrolled in a Course. Initially, your database can have a table of Courses and a table of Students. On the Courses table, each record would have a column (i.e. connection field) for Students who are enrolled in that course. That setup is simple and it works.

However, let’s say you want to capture when the student enrolled and unenrolled in the course; then a Students field is no longer appropriate and it’s time to create another object. For example you could create an Enrollments object, allowing you to keep Enrollment records indicating who enrolled into what course and when. The Enrollments object would be connected to a Student and a Course, and have additional fields such as Enrollment Date.

Let me know if I missed anything.

Knack Pros

I like the way you think. Thank you for confirming that this is a viable way to go.

I have ended up with a similar solution to your idea, except I abstracted it a bit more. I have objects EVENTS, PEOPLE, and ASSOCIATIONS. The ASSOCIATIONS link a person to an event in a particular way. A PERSON can be an instructor for a course, or a student taking a course, a guest speaker at a talk, etc. This way I can handle any time of involvement.

I may even put committees into the events list so I can have a record reflecting that a person was a member of a committee during a specific semester.

1 Like