Wrapping your head around the way that relational data works can be confusing. Let’s use an analogy to try to illustrate it.
Think of two files. One file contains a list of actors. In this file, each actor has both a unique ID and name. The second file is a list of movies. Each movie in this file also has both a unique ID and a name. Actors, as we know, can be in multiple movies, and a movie can have multiple actors. Let’s use these two imaginary data sets to look into how 1:1, 1:many, and many:many relationships in relational data work.
To set up a 1:many relationship between actors and movies, let’s start by adding a field to the actor file, and let’s call that field “movie”. Now, our Actor file has three pieces of data for each actor: a name, a unique ID, and a movie. In this list, we have now made it so that each actor can be associated with one movie. However, since we do not require this field to be unique, multiple actors can be associated with the same movie. If we then link the movie field (in the Actor file) to the Movie file, when we upload our list of actors, the “movie” field in the Actor file can contain information from the Movie file. In the Movie file, the movie ID is unique, so it will link to only one movie.
To enforce a 1:1 relationship between these two data sets, you would force each “movie” field in the Actor file to be unique, making each actor associated with only one movie, and each movie associated with only one actor. In Flatfile, the 1:1 and 1:many relationship is simply controlled by turning the “set field as unique” checkbox on or off.
If you upload an Actor file and the “movie” field in the actor file does not exist in the Movie file, then Flatfile’s upserting feature will automatically create a new record in the Movie file with the new ID so that the relationship can be made. There’s nothing you have to do to enable upserting, it will be done as needed whenever you create a relationship between files.
Many:many relationships can exist in a couple of ways so it’s probably best to discuss your use-case to come up with a specific answer. In our example, you could have multiple movie fields in the actor file so that each actor can be linked to multiple movies. Or you could go the other way and create a relationship between the movie file and the actor file so that each movie can be associated with one or more actors.