I suspect this is one of these questions to which there is not going to be a single definitive answer. But I'm hoping to gain some insight into what might generally be considered best practice, most of the time anyways. So, thanks in advance to those who weigh in on this.
I have two types of folders for storing records. Personal Folders and Team Folders.
There are two ways that I could represent this in the database...
Option 1: Two separate and unrelated folder tables.
personalFolders( id, userId, title ) teamFolders( id, teamId, title ) records( id, folderId, folderType, title )
Option 2: A common folder table with two join tables
folders( id, title ) personalFolders( folderId->folders.id, userId ) teamFolders( folderId->folders.id, teamId ) records( id, folderId->folders.id, title )
My question is: Generally, what schema would be considered best practice?
(My gut is telling me to go with option 2, because then I can keep the foreign key constraint in the records table. But, are there situations where it might be better to go with option 1?)