Mysql – What would be the better schema to represent this data

MySQLschema

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

Best Answer

Without knowing complete and exact requirements, we cannot design a complete data model. Based on my understanding from question, the following rules can be derived.

  • Each folder must have a folder type
  • Each folder type may contain more than one folder
  • Each folder must belongs to an owner
  • Each owner may contain more than one folder
  • Each owner must belongs to an owner type (either individual or team)
  • Each record must belongs to a folder
  • Each folder may contain more than one records

Here, I am not clear about about few things, such as:

  • Whether a record can be a part of more than one folder
  • Whether a folder belongs to both individual and team
  • Whether folder type other meaning such as archival or sales or something else
  • Whether is there any chance of adding more folder type or owner type (group, external, vendor, client, etc)

I am designed the below model, hoping future changes are adoptable without much changes in the design.

Data model for record storage on folder

My suggestion is to start from conceptual data model, regardless of the size of the project. More questioned you answered on this process, more better the application will be.

I hope this answer will be helpful. Thanks!

Related Question