Hi all, thanks again for the input, I really appreciate it.
Bruno's right, the advantages for extended resources will be useful for me, particularly friendly URL's & being able to easily implement commenting.
OK here's an example of the DB structure that is actually very similar to what I'm thinking of doing:
http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server
In my case it'll be something more like this:
- there are many types of facilities (in this example tennis courts & swimming pools)
- the table "Facilities" will hold all of the information that is common across all facilities
- sub-tables will hold the information specific to each type of facility
- the sub-table data will also need to be searchable
<object class="Facilities" table="facilities" extends="xPDOObject">
<field key="facility_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native" />
<field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
<field key="description" dbtype="text" phptype="string" null="true" />
<field key="address" dbtype="text" phptype="string" null="true" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
<column key="facility_id" collation="A" null="false" />
</index>
<composite alias="Tennis_Courts" class="Tennis_Courts" local="facility_id" foreign="tennis_court_id" cardinality="one" owner="local" />
<composite alias="Swimming_Pools" class="Swimming_Pools" local="facility_id" foreign="swimming_pool_id" cardinality="one" owner="local" /> <!-- I'm not sure if you can have two composite tags? -->
</object>
<object class="Tennis_Courts" table="tennis_courts" extends="xPDOObject">
<field key="tennis_court_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native" />
<field key="number_of_courts" dbtype="tinyint" precision="3" attributes="unsigned" phptype="integer" null="true" />
<field key="surface_type" dbtype="varchar" precision="100" phptype="string" null="true" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
<column key="tennis_court_id" collation="A" null="false" />
</index>
<aggregate alias="Facilities" class="Facilities" local="tennis_court_id" foreign="facility_id" cardinality="one" owner="foreign" />
</object>
<object class="Swimming_Pools" table="swimming_pools" extends="xPDOObject">
<field key="swimming_pool_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native" />
<field key="pool_length" dbtype="tinyint" precision="4" attributes="unsigned" phptype="integer" null="true" />
<field key="indoor_pool" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
<column key="swimming_pool_id" collation="A" null="false" />
</index>
<aggregate alias="Facilities" class="Facilities" local="swimming_pool_id" foreign="facility_id" cardinality="one" owner="foreign" />
</object>
So there will always be a 1-to-1 mapping but it could be to any of the sub-tables.
I hope this makes sense! And that the xPDO def is right, I've never worked with it before
