Topic: How do I do this in MySQL?

[Rob]

Let's say I have a table where I'm recording certain events that have happened to a "thing".

Some events will be being moved from one place to another, but other events will be, for example, a repair being done. To do this there will be an event type field. What I want to happen is that when the event type is "repair" for example, it forces there to be another entry in another field that points to a repair detail table holding the detail of the repair.

To paraphrase, when the event type is "movement" the "repairID" field can be empty. When the event type is "repair" the "repairID" field must have an entry.

I know I could do this at application level, but it feels neater at database level.

There's a further question around creating the repair detail, but let's sort this one first.

Tried googling, but wasn't really sure of the correct term.

[Steve]

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html  ??

[ober]

The database isn't going to do that for you.  You have increment fields but they're kind of dumb in regards to other data in the database.  Either way, what you're describing isn't exactly normalized but I'd need a better description of the data to fully understand the problem and suggest a solution.

[Rob]

I don't want to post too much detail about the data (paranoia) so I'll try to come up with an example.

[Rob]

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html  ??

Thanks.

That looks promising (and I think I finished up there when I was searching earlier).

I guess it would be like

[pseudo code] If [event_type_field]="repair" then [repair_ID_field]=!NULL[/pseudo code]

[Rob]

Ober, I guess to be normalized, I should have a "movements" table and a "repairs" table instead of treating them both as events, no? New to this, remember.

[Mike]

Let's say I have a table where I'm recording certain events that have happened to a "thing".

Some events will be being moved from one place to another, but other events will be, for example, a repair being done. To do this there will be an event type field. What I want to happen is that when the event type is "repair" for example, it forces there to be another entry in another field that points to a repair detail table holding the detail of the repair.

To paraphrase, when the event type is "movement" the "repairID" field can be empty. When the event type is "repair" the "repairID" field must have an entry.

I know I could do this at application level, but it feels neater at database level.

There's a further question around creating the repair detail, but let's sort this one first.

Tried googling, but wasn't really sure of the correct term.

I don't know of any way in MySQL to validate that data.  But if you want to select that data it is pretty easy.

Code: [Select]

SELECT IF(event.type = 'repair', repair.field, event.type) AS eventtype
FROM event
LEFT JOIN repair USING (repair_id)

I believe the above isn't standard SQL which would use

Code: [Select]

CASE WHEN event.type='repair' THEN repair.field ELSE event.type END CASE
But I don't remember 100% as I haven't used case in a long time.

[Rob]

Thanks Mike.

[Perspective]

>>What I want to happen is that when the event type is "repair" for example, it forces there to be another entry in another field that points to a repair detail table holding the detail of the repair.

Do you want a repair detail to be created when the event is changed to 'repair' ? You could do that with a trigger. If you want to disallow the event type to be changed to 'repair' unless the repair detail exists you'll have to do that with external business logic.

[Rob]

>>What I want to happen is that when the event type is "repair" for example, it forces there to be another entry in another field that points to a repair detail table holding the detail of the repair.

Do you want a repair detail to be created when the event is changed to 'repair' ? You could do that with a trigger. If you want to disallow the event type to be changed to 'repair' unless the repair detail exists you'll have to do that with external business logic.

Yes, I think that is what I want, or rather when an event is created whose status is "repair" then create the corresponding detail. Can I still do that with triggers?

[Perspective]

Yes, that can be done with triggers.

e.g., http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table

[Rob]

Wait, I have that completely ass-backwards thinking about it. What needs to happen is that when a repair detail record is created THEN a corresponding event whose status is "repair" needs to be created. Nonetheless, it looks like it would work the same way. Thanks Perspective.

Yes, that can be done with triggers.

e.g., http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table

Perfect. Thank you.

[Rob]

Brilliant! Triggers worked a treat (after I figured out how to add one - dropped into the delimiter trap).

Now when I add a repair event, I automatically get an entry in my events that points to it. Superb.

 

[Perspective]

Not that I don't trust your DB design skills, but make sure there is a foreign key relationship between the repair record and the repair detail record. The latter should depend on the former for existence (weak vs strong entities if you're reading any ER literature).

[Rob]

Not that I don't trust your DB design skills, but make sure there is a foreign key relationship between the repair record and the repair detail record. The latter should depend on the former for existence (weak vs strong entities if you're reading any ER literature).

No idea what ER literature is, but yes, I have an FK relationship there. Can you expand on "The latter should depend on the former for existence" slightly please? Does this mean if the repair record is deleted the repair detail should be deleted automagically too?