INSERT with Subtype SuperType

I am currently working with a db that utilizes a subtype / supertype structure. I am wondering the best approac开发者_开发知识库h to handling INSERTs. Do I keep the population of multiple tables in the SQL itself, or with PHP, or even a combo of the two?

I am using MySQL / PHP (w/ Yii Framework)

///EDIT///

Don't know whats up with a down vote. Didn't realize the importance of any tables as this is a general approach question. But here is an example. If a Party is created how do Business and or User get populated with that PartyId? SQL/PHP/Both?

The "standard" approach is to create one updatable view for each subtype. Each updatable view joins the supertype with one subtype.

Then application code usually uses the view, not the base tables.

On most platforms, that means you need to write some triggers.

I complely don't get what's your question.

If you need to add a row on Party table then you do:

INSERT INTO party [...]

Then if you need an insert into Business you do

INSERT INTO business [...]

Simply as that yes.

If you want to be safe both queries will be runned start a transaction

I'm assuming since you put it in the Yii tag that you are actually using Yii? The normal thing to do would be to create an activerecord class for each of those. You'll still have to transfer the resulting PartyId to one of the other 2 classes. Most of the time this is done using an assignment.

$oTransaction = Yii::app()->db->beginTransaction;
$oParty->save();
if ($oParty->PartyType == ...)
{
   $oBusiness->BusinessId = $oParty->PartyId;
   $oBusiness->save();
}
else
   Do User things
$oTransaction->commit();

There are probably better was to do this, perhaps even use inheritance on the AR classes, but I don't know if that will save any work. Best thing is to give your Party model a function that will return either a Business or a User instance I think.

The cleanest way would also be to save the business/user in the afterSave() method of the Part, you have the PartyId there. But then your Party model needs to have the data to do that.

You might want to work through a couple of the tutorials to better understand how Yii works with relational data models. See http://www.larryullman.com/series/learning-the-yii-framework/ and http://www.yiiframework.com/doc/blog/

For now, you don't really seem to have a Yii question but a more basic database modeling question -- I'm sure there are plenty of tutorials on that as well, but if you are interested in learning Yii at the same time, those links above are a good start.