Hello! In my application I use 2 types of objects (Assembly, Part) which are hierachical related to each other. Each assembly can have many assemblies and/or parts:
Assembly
-> Part
-> Assembly
-> Part
-> Part
-> Part
-> Part
The objects have common attributes: id, parent_id, name, created, author
But in addition the Assembly-object has the attributes: color, icon
And the Part-object has the attributes: number, visible
What is the best practice to build such a structure in the database AND as models? Especially in regards of: find, create, update, delete, …
Using ActiveRecord I would create 3 models: Baseobject, Assembly, Part. I think this would result in a complicated model handling. Especially when creating/reading/updating/deleting/searching a model. Because I always need to take the related models in account.
The usual way to store it is to have a single table for all items, let us call it Component. This table has a column for type (0=part, 1=assembly) and then columns for all of the properties of both types, some of which will be null if they don’t apply. There will also be a self-referencing key column called parent, which contains the row id for the Component that is the parent - or null if it is a top level item. When loading parts from the database, you can easily see if type=Assembly, you need to subload the parts and assemblies where parent id = current id and then if it is type=Part, you just load itself.
I think Yii will take care of the relationships if you setup the foreign key correctly in your database.
Thank you! The method you described about the relation with child/parent is perfect! But now I am a little bit confused: is it really best practice to use just ONE table for all different types (parts, assemblys,…) ?