0

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

  • Write the SELECTs you will need; that will help you decide. – Rick James Oct 21 '17 at 2:55
  • Edit your title to be specific about your particular question and its core issue. – Basil Bourque Oct 22 '17 at 5:57
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.