Need Schema Help: Relating 2 Custom Tables, and the User Table

99 Posts

smg6511 Reply #1, 15 years, 4 months ago

Greetings,

Like many, I’m on the beginning end of the learning curve for understanding/using xPDO. Right now, it’s a mind-bender for me!

So I’ve not gone so far as to try to parse this schema into the requisite classes, etc. ... I just want know if it’s done correctly. I’ve attached a basic table design for reference. Basically, I’m creating an itinerary planner in which users can save references to particular resources, attaching dates, times, notes, etc. along the way. A user may save any number of named itineraries, in which any number of unique items may be saved. Only registered site users can create/save itineraries to the db. Thus, when a user is deleted, the related itineraries and items should also be deleted. And, of course, when a user deletes a particular itinerary, the related items should be deleted.

So here’s the schema so far:

<model package="SA_modPlanner" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" phpdoc-package="SA_modPlanner" phpdoc-subpackage="model">

    <object class="saItinerary" table="itineraries" extends="xPDOSimpleObject">

        <field key="userId" dbtype="int" precision="5" phptype="integer" null="false" default="" index="index" />
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <composite alias="ItineraryItems" class="saItineraryItem" local="id" foreign="itineraryId" cardinality="many" owner="local" />
        <aggregate alias="User" class="modUser" local="id" foreign="id" cardinality="one" owner="foreign" />

    </object>

    <object class="saItineraryItem" table="itinerary_items" extends="xPDOSimpleObject">

        <field key="itineraryId" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" index="index" />
        <field key="source" dbtype="tinyint" precision="2" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="record" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="fkey" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="category" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="sdate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="edate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="stime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="etime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="notes" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="timestamp" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <aggregate alias="Itinerary" class="saItinerary" local="itineraryId" foreign="id" cardinality="one" owner="foreign" />

    </object>

</model>

Any feedback is appreciated

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

4 Posts

pureppl Reply #2, 15 years, 4 months ago

Hello

I’ve spotted an error:

<aggregate alias="User" class="modUser" local="id" foreign="id" cardinality="one" owner="foreign" />

should be

<aggregate alias="User" class="modUser" local="userId" foreign="id" cardinality="one" owner="foreign" />

MODX Staff
10,725 Posts

opengeek Reply #3, 15 years, 4 months ago

If you are using xPDOSimpleObject, this automatically adds an id Primary Key column to each table. Your additional tablenameId columns are not primary keys.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

99 Posts

smg6511 Reply #4, 15 years, 4 months ago

@pureppl: Thank you for taking a look and spotting that!

@Jason: Yes, the "userId" in my "saItinerary" class and the "itineraryId" in my "saItineraryItem" are not meant to be PKs, as the same id can occur multiple times in both tables (i.e., a user may have multiple itineraries, and an itinerary will have multiple items). Do I need to change the index attribute to index="fk" in those? And do all ids in aggregate and composite tags need to be PKs? Thanks for your feedback!

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

MODX Staff
10,725 Posts

opengeek Reply #5, 15 years, 4 months ago

Quote from: smg6511 at Jun 03, 2011, 09:45 AM

@pureppl: Thank you for taking a look and spotting that!

@Jason: Yes, the "userId" in my "saItinerary" class and the "itineraryId" in my "saItineraryItem" are not meant to be PKs, as the same id can occur multiple times in both tables (i.e., a user may have multiple itineraries, and an itinerary will have multiple items). Do I need to change the index attribute to index="fk" in those? And do all ids in aggregate and composite tags need to be PKs? Thanks for your feedback!

Sorry, must have looked at this too early—I see now... They do not need to be fk’s though it might be useful to have those marked that way in your metadata. However, those are the old index creation methods from earlier releases of xPDO. The latest uses a new schema version, 1.1, that specifies indexes as separate elements and you’ll need to update your model to 1.1 if you are expecting to make this Extra work with other databases (like sqlsrv that we now support in xPDO 2.1/MODX 2.1 releases). See the index elements in the latest MODX schema files for an example. The attribute version="1.1" on your model element will tell the generator to look for the new index elements in mysql schemas. Documentation updates coming regarding this...

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

99 Posts

smg6511 Reply #6, 15 years, 4 months ago

Thanks for the info, Jason. I’ve looked at the current modx schema and, based on what you’re saying, it seems that the index attr on field elements is deprecated (though I still see them used in the current schema). Is my assumption correct? My updated model under that assumption is:

<?xml version="1.0" encoding="UTF-8"?>

<model version="1.1" package="SA_modPlanner" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" phpdoc-package="SA_modPlanner" phpdoc-subpackage="model">

    <object class="saItinerary" table="itineraries" extends="xPDOSimpleObject">

        <field key="userId" dbtype="int" precision="5" phptype="integer" null="false" default="" />
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <index alias="userId" name="userId" primary="false" unique="false" type="BTREE">
            <column key="userId" collation="A" null="false" />
        </index>

        <composite alias="ItineraryItems" class="saItineraryItem" local="id" foreign="itineraryId" cardinality="many" owner="local" />
        <aggregate alias="User" class="modUser" local="userId" foreign="id" cardinality="one" owner="foreign" />

    </object>

    <object class="saItineraryItem" table="itinerary_items" extends="xPDOSimpleObject">

        <field key="itineraryId" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="source" dbtype="tinyint" precision="2" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="record" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="fkey" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="category" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="sdate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="edate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="stime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="etime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="notes" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="timestamp" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <index alias="itineraryId" name="itineraryId" primary="false" unique="false" type="BTREE">
            <column key="itineraryId" collation="A" null="false" />
        </index>

        <aggregate alias="Itinerary" class="saItinerary" local="itineraryId" foreign="id" cardinality="one" owner="foreign" />

    </object>

</model>

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

MODX Staff
10,725 Posts

opengeek Reply #7, 15 years, 4 months ago

That is correct; they are deprecated, but still used if you are using a model with no version attribute (or a version attribute with a value less than 1.1). Just add version="1.1" to make the generator use the new index elements in your schema.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

99 Posts

smg6511 Reply #8, 15 years, 4 months ago

OK, so far-so good. I’ve successfully run the build script and the classes have been created. One point of confusion: Documentation I’ve read had led me to think that the build would also create the db tables based on the schema. However, this seems to not be so (unless I’ve missed a step somewhere). Please confirm whether tables are supposed to be created on the fly, or if they need to be manually created. Merci!

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

64 Posts

Kristoffer Reply #9, 15 years, 4 months ago

The tables will be created when you are using them for the first time. That means that they will be created when you save (or maybe create) your first object.

99 Posts

smg6511 Reply #10, 15 years, 4 months ago

Ahh, got ya. The tables were created on my first test calls to GetCollection.

Now in my testing environment, I’m having another interesting issue: I’m testing saving data to the "itineraries" table and I’m getting duplicate data written each time. Here’s the code for the test save:

$itinerary = $modx->newObject('saItinerary');
$itinerary->fromArray(array(
    'name' => 'Yet Another Test Itinerary',
    'userId' => $uid,
    'createdon' => time()
));
$itinerary->save();

Any idea why this might happen, given my schema (posted earlier in this thread)?

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.