-1

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?)

user1031947
  • 6,294
  • 16
  • 55
  • 88
  • Can a personal folder and a team folder have the same title? The more general question is: what is the natural key for folders or for each different type of folder? – rd_nielsen Oct 21 '17 at 02:48
  • @rd_nielson -- yes, they can have the same titles. ID is the primary key. – user1031947 Oct 21 '17 at 02:52
  • So in your Option 2, would you create a unique index on title and have both the personal folder and the team folder that have the same title use the same id, or would you allow two different ids with the same title? And if the latter, are you planning on writing triggers to ensure that the personal folder and team folder don't both refer to the same id? Relatedly, are there other attributes for personal or team folders other than the title? – rd_nielsen Oct 21 '17 at 02:59
  • The latter. And in option 2, triggers wouldn't be necessary, since both personalFolders and teamFolders have the foreign key folderId that points to folders.id. (Sorry if my notation is not clear.) Yes, there are other attributes, but I have removed them to try and simplify the example. – user1031947 Oct 21 '17 at 03:07
  • Do personal folders and team folders have different attribute *types* or do they have all the same attribute types, but a personal folder and a team folder with the same title might have different attribute *values*? The point of these questions is that an appropriate data structure is dependent on the details of all of the data. Conceptual simplifications can lead you astray. Assigning a surrogate key 'id' column as a primary key is not an automatic guarantee that you will achieve even first normal form. – rd_nielsen Oct 21 '17 at 03:16
  • Yes, personalFolders and teamFolders have different attribute types, beyond just userId & teamId. The full list is extensive, so I neglected to include it here, but you're right, this example is on the too simplified side. Still, I think I have gotten the conversation that I needed, to move forwards with this. Appreciate your input. – user1031947 Oct 21 '17 at 03:35
  • This is a faq, google sql/database subtypes/inheritance etc etc. Times a trillion. Also under antipattern multiple/many/two FKs to multiple/many/two tables. – philipxy Oct 21 '17 at 05:30

1 Answers1

0

I would actually make one table for folders, that contained a column for both userId and teamId:

folders (id, title, userId, teamId)
records (id, folder.id, title)

You can determine if the folder is a personal folder or team folder by checking if the userId or teamId column is NULL or has a value.

This has the advantage that a folder can be both a personal and team folder, without you having to store the same folder in two tables. You can also have the FK constraint in the records table, which you should have. This would allow you to easily select all folder (both personal and team) without any joins or a union.

This is basically the same as your option 2, but it simplifies it by eliminating the need for the extra join tables.

I personally don't see any advantages to option 1, unless perhaps you have a ridiculous amount of data and breaking them into separate tables could improve performance.

patrick3853
  • 1,100
  • 9
  • 17
  • One thing I should have asked you though, if a folder can belong to multiple users or multiple teams, then this does not work and you would want to use your option 2. However, if a folder should only ever belong a single user or single team, then there's no reason to add the complexity of join tables. – patrick3853 Oct 21 '17 at 03:09
  • Thanks for the answer, this makes good sense looking at the example I provided. You guessed right though, in fact folders can belong to multiple people, so I guess I will go with option 2 and the joins. – user1031947 Oct 21 '17 at 03:22
  • Yeah, if user / team is a many to one relationship to folders, join tables are your only option to normalize the data and avoid duplicating folder records. – patrick3853 Oct 21 '17 at 03:27