Re: constraining one correct answer but multiple invalid answers

dombrooks wrote:
> You could create a function-based index index which has two arguments -
> the question and id and the yes/no flag.
> This would then return the question id when the when the flag is 'Y'
> but NULL otherwise.
>
> Then create a unique function-based index using this function passing
> in the question id column and the yes/no flag column.
>
> Null entries are not entered into the index and an entry is only made
> containing the question id when the flag is yes.

To me at least your suggestion seems to fit into the category of putting lipstick on a pig. There's something fundamentally wrong with the current ERD design as noted by the OP.

To produce scalable applications you have to get the design part right in the first place.

In the case cited by the OP they have a supertype with 2 subtypes. You either support that in a relational system by using some kind of type identifier column in one table or break the subtypes into multiple tables. That's the only way to do it without kludging together something that has inherent scalability limitations built into it. Received on Thu Jan 04 2007 - 06:53:27 CST