INSERT with Subtype SuperType

Question

I am currently working with a db that utilizes a subtype / supertype structure. I am wondering the best approach 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?

enter image description here

Please show us the tables you have, question cannot be answered in its current form. — Johan, Jun 03 '11 at 19:06

score 2 · Accepted Answer · answered Jun 04 '11 at 10:13

2

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.

answered Jun 04 '11 at 10:13

Mike Sherrill 'Cat Recall'

91,602
17
122
185

@Catcall, thanks for the info. Got some more research to do now since I don't have experience with this approach but I appreciate you understanding the question as it applied to what method to take rather then just say it is easy or require table design. Guess you know a bit about databases! – enfield Jun 04 '11 at 22:14
@enfield: You can skim my answers by clicking on "Catcall". Some people seem to think I know a little. – Mike Sherrill 'Cat Recall' Jun 05 '11 at 00:37
@Catcall, after doing some searching and reading on the net I found your examples [here](http://stackoverflow.com/questions/5692924/data-modelling-draft-quote-order-invoice) and [here](http://stackoverflow.com/questions/5466163/same-data-from-different-entities-in-database-best-practice-phone-numbers-exa/5471265#5471265) to be the best sources beside the actual MySQL site but still lost. If you have time could you help me with the code for the example above? I need to populate BusinessId & UserId when a new Party is created and assigned a PartyType of either B (Business) or U (User). – enfield Jun 05 '11 at 05:26
@Catcall continued... I was able to accomplish this simply using a trigger but now after reading up on Views I see why you mentioned that approach. Coding it is not something I understand just yet though. – enfield Jun 05 '11 at 05:33
@enfield: I don't think MySQL supports INSTEAD OF triggers. You might have to manage the inserts, updates, and deletes using a stored proc instead of using triggers. – Mike Sherrill 'Cat Recall' Jun 06 '11 at 12:09
@Catcall Is this what you are referring to? [Can you insert into views that are based on joins?](http://dev.mysql.com/doc/refman/5.5/en/faqs-views.html#qandaitem-B-6-1-6). I am accepting your answer for this as you answered in regards to the method/approach I was questioning and opening another to see if someone knows how to execute it. – enfield Jun 06 '11 at 20:18
@enfield: Yup. Looks like you'll have to go with a stored procedure. – Mike Sherrill 'Cat Recall' Jun 06 '11 at 20:20

dynamic · Answer 2 · 2011-06-03T22:46:24.047

0

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

edited Jun 03 '11 at 22:46

answered Jun 03 '11 at 22:40

dynamic

46,985
55
154
231

wish it was that simple...maybe in your mind. If I add a new Party how do I ensure that what was created at that moment in the Party table properly populates either the Business or User tables (depending on the PartyType selected) – enfield Jun 03 '11 at 22:46
@enfield: added last phrase in my answer ;) – dynamic Jun 03 '11 at 22:46

score 0 · Answer 3 · answered Jun 04 '11 at 04:54

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.

Yes I am using Yii as stated in my question. Thanks for the input. I am looking at CDbTransaction as that seems to be the approach for transactions. I will take your input and that from Catcall and see if I can figure out a solution. — enfield, Jun 04 '11 at 22:11

score 0 · Answer 4 · answered Jun 07 '11 at 02:10

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.

INSERT with Subtype SuperType

4 Answers4