guarantee child records either in one table or another, but not both?

Question

I have a table with two child tables. For each record in the parent table, I want one and only one record in one of the child tables -- not one in each, not none. How to I define that?

Here's the backstory. Feel free to criticize this implementation, but please answer the question above, because this isn't the only time I've encountered it:

I have a database that holds data pertaining to user surveys. It was originally designed with one authentication method for starting a survey. Since then, requirements have changed, and now there are two different ways someone could sign on to start a survey.

Originally I captured the authentication token in a column in the survey table. Since requirements changed, there are three other bits of data that I want to capture in authentication. So for each record in the survey table, I'm either going to have one token, or a set of three. All four of these are of different types, so my thought was, instead of having four columns where either one is going to be null, or three are going to be null ( or even worse, a bad mashup of either of those scenarios ), I would have two child tables, one for holding the single authentication token, the other for holding the three. Problem is, I don't know offhand how to define that in DDL.

I'm using MySQL, so maybe there's a feature that MySQL doesn't implement that lets me do this.

Answer 1

There are multiple ways you can implement this

Boolean Flag Logic

You can have a boolean flag in ParentTable called ChildTableDataInserted and you can update this to TRUE if row is inserted in any one table. Only insert into a child row if the ChildTableDataInserted is False. This of-course is dependent on all inserts using the stored procedures that you create and can easily be violated, but overhead on the database is small.

Triggers

Put a BEFORE INSERT on the data insert to both of the child tables, and if your fault condition is met, you can raise an error for the insert.

I personally avoid triggers unless I have absolutely no other option and this seems like a good place to implement triggers.

Read more on triggers here

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html