guarantee child records either in one table or another, but not both?
- by user151841
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.
