Q: Best way to store whether data exists in a relational database? If we ask the following question of an individual: "Does that person have children?" There are three possible answers: "Yes", "No", "Unknown". With a table design that stores the person as a record in a people table, and a child as a record in a children table with a foreign key to the people table, we cannot distinguish between "No" and "Unknown". Both answers imply no rows in the children table.
What is the best way to store the answer to that question that can distinguish between the three states? My current thought is storing that information as a separate field on the people table, but that opens up data integrity issues.
