This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Hi, I'm new to design patterns. Just read composite pattern which is good at handling recurive data. But I don't know how to map composite objects to database? For example, an Folder object composes of multiple Folder objects which contains Folders and Files and so on. How to design the database to persist the data? Thank you for your help. Please provide some related links if possible. [ April 12, 2004: Message edited by: richard chang ]
There are three common designs that I am aware of. The easiest is a single table with a single parent reference. Multiple children can have the same parent, giving you a one to many relationship like a directory structure. As a Class it would look like this:
The second common design has a table that defines the folder, and a separate joining table that defines the parent-child relationships:
This design can also be many-to-many if desired. Two big problems with this design is where to start, and the cost of traversing the tree. THe 'where to start' problem is usually solved by having either a root node 'id=1 is the root and everything comes off it', or defining any folder with a null parent as a root node. The second problem is that to build a list of all of the folders, you have to start with the root folders, then load all of their children, then all of their children, etc etc until you load the whole tree. Since each query is a separate database query, it can be a significant problem if you constantly load large sections of the structure (ignore caching, assume the data is changing constantly). This brings us to the third design, which I've encountered, but never used. It cheats by denormalising the first N steps of the directory structure and then loading anything beyond this in one of the previous ways. Lets say you're designing a news site, and in your design you define 4 layers of menus that gets you to 90% of your stories. You can define the extra data like this to allow you to load all of these folders in a single database statement:
The folderIds still load from the Folder table, and the bottom folder can still have children, but it can significantly reduce the impact of needing to reload the entire tree structure. Hope this is what you were looking for. Dave