CODEWITHSUNDEEP
mysqldatabasedata-structurestreehierarchy
Dylan Stables
Dylan Stables

Reputation: 21

MySQL Structure for tree with different types

I've been tasked with building a tree structure. I'm inexperienced with this thing and was wondering if there's any glaringly obvious issues with my approach.

So I have a large table of files (and fields regarding regularly use data in that file) which must remain the same, and I'm creating a structure where there's folders, so folders can contain X number of files and X number of folders. But the files can have no children.

With the structure I was thinking I'd have to handle either a null folderId or fileId but other than that I think its okay.

Desired output is that the tree structure is to be lazy loaded over a REST API, so a user can click a node and Ill retrieve all its children using an ID. I'm not sure my design is ideal so I'm after some feedback.

I was going to build my table structure like so (sorry in advance for bad formatting):

FileTable

FolderTable

ItemTable

TreeTable

Upvotes: 0

Views: 237

Answers (1)

esantos
esantos

Reputation: 162

I'm thinking of using just one table

DirObject

with the following fields:

  • Id
  • ParentId #Link to parent object -- parent should be of type 'Folder'
  • ObjectType #Folder or File
  • Version
  • OwnerUser
  • OwnerGroup
  • Permissions
  • LastUpdate
  • FileSize
  • Status
  • Path

Update:

Since you will be needing a separate table for the files, try this:

Folders

  • Id
  • ParentId #Link to parent folder
  • OwnerUser
  • OwnerGroup
  • Permissions
  • LastUpdate
  • Status
  • Path

Files

  • Id
  • FolderId #Foreign key from Folders table
  • OwnerUser
  • OwnerGroup
  • Permissions
  • LastUpdate
  • Status
  • Path

Note:

Value of Path should just be like how normal directories work \folder1\folder2\folder3\filename

This will help when you want to get the size of a certain folder. your query will look like

SELECT SUM(FileSize) from db.files where path like '\path\of\folder\%';

Upvotes: 1

Related Questions