Objects with common attributes

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:


 -> 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, …

I thought about using such a database structure:

  1. baseobject: id, parent_id, name, created, author

  2. assembly: id, baseobject_id, color, icon

  3. part: id, baseobject_id, number, visible

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.

How would you design your models?

Does nobody has an answer? Too complicated?! I face this problem in many of my projects… nobody else?

This is called the Composite Pattern http://en.wikipedia.org/wiki/Composite_pattern

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