Structuring PHP code to link object creation and db writes?
I am writing a site (PHP + MySQL) where some objects (say, articles) are organized in sets. Each article belong to a singleton set. Each set can be stand alone or belong to a larger set. A set is either a singleton or it is a collection of sets. To keep track of the relationships between the sets, I use a closure table that tracks all "family links". The table has an ancestor field, a descendant field, and a length field which counts the degrees of separation between the ancestor and the descendant.
- Each new article implies the creation of a new singleton set.
- Each new set implies the creation of a new closure table entry where ancestor and descendant are the same and where length=0. New sets can be created independently of articles since sets can be collection of existing sets.
I want to structure my PHP program so that each time I write a new article in the db, I automatically write a new set, and each time I load a new set, I automatically write a new closure table entry.
What is the right way to structure my code? Is it to create nested objects (the article object includes a set, which itself include a closure) and a recursive function to write on the database which would write the nested object first (and where the end condition would be if the object is a closure table entry)? Is there a way to fold those recursive writes inside a single transaction?
Is there an existing pattern I can use?
The answer is pretty straightforward. The nested objects are somewhat correct but in reverse order.
Look at it in a is-a relation. An article is part of a set, but in one set can be multiple articles. A set can be part of another set, but in this other set can be multiple lower sets. The lower should not know the higher.
How does the structure look like?
CREATE TABLE article ( articleID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, // some other columns that don't matter for this structure answer ); CREATE TABLE set ( setID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, // some other columns that don't matter ); CREATE TABLE article_to_set ( articleID INT(10) NOT NULL, setID INT(10) NOT NULL, UNIQUE KEY (articleID, setID) ); CREATE TABLE set_to_set ( setID INT(10) NOT NULL, parentSetID INT(10) NOT NULL, UNIQUE KEY (setID, parentSetID) );
How are the PHP classes structured?
The Set knows from database if an article belongs to it (standalone set) or other sets (collection of sets). So it has two properties. The first is a nullable article reference variable and the second an array of the sub-set objects (can be empty). The Article knows from database only it's own properties and it does know nothing from the relation to any set.
How is the linking handled?
If you create an article in the controller ArticleAdd, you create the article and a standalone set. Then you insert a row with both the articleID and the setID into the table article_to_set. If you create a new set a without an article, it is empty on begin. If you then add new sets (direct or via articles), you can put them into the set a by adding rows to the table set_to_set, where the parentSetID is the ID of the set a and setID the ID of the set you put into the set a.
This solution doesn't use a closure table but it makes the design much better for future edits. And putting one set into another can easily be done by adding one row.