How do I model a feature that tracks the verified absence of aggregate entities?

Let's say I have a Person table and a Song table and a Person_Song table that is a many-to-many join table between the two other tables.

If a person knows all the lyrics to a song, then a record is inserted into the Person_Song table. If the person knows 3 songs, then 3 records are inserted. Fine...easy stuff.

So let's say that we send out a survey to 100 people. Most people will fill out the survey and they will enter 2 or 3 songs. Some people will not fill out the survey at all. And then there are some people who will fill out the survey and note "I don't know the lyrics to any songs".

I want to differentiate the situations where they did not specify any songs and when they explicitly state that they know zero songs.

What is the best way to track this condition in a database? Is there a well known pattern for this?

Here are some thoughts that I have:

1) Create an entry in the Song table for "No Known Songs" (ID == 0 or something like that). If they explicitly state that they don't know any songs, then insert a single record in the Person_Song table. I would also need to enforce (via db constraints or code) that this record is NOT inserted if the person has other records assigned to him. This is "hacky" for sure, but I think it would work.

2) Create a separate table called PersonNoKnownSongStatus which has a one-to-zero-or-one relationship to the Person table. If this flag is set for a Person, then a record is inserted. If 开发者_如何学Cwe retroactively add records to the Person_Song table, then we would delete this record (or update a flag in the record). This would work and seems less hacky, but it is about the same amount of work as the first solution.

What further complicates my need is that I need to track other aggreate entities as well. So imagine we have a Movie table and a Person_Movie join table that keeps track of what movies a person has seen. Also imagine that we have a Book table and Person_Book join table that keeps track of what books a person has read. For all of these entities, I need to track whether a person has explicitly acknowledged that he is not familiar with any form of the aggregate entity.

So I suppose this opens the door for a third design:

3) Create a separate table called PersonNoKnownEntityStatus, which has a many-to-many relationship between the Person table and Entity tables, where Entity contains the lookup records Song, Movie and Book. If the person acknowledges that they have not seen any movies, then a record is inserted for the Person ID and the associated Entity record for the Movie row. Ditto for Books and Songs...

Is there another design that I should consider?

(edit) I forgot to mention the possibility of adding new columns to the Person table. These could just be boolean flags or even a bitmask that represents the explicit absence of the entities. I wanted to avoid this solution because there is already a lot of contention on the Person table.

I dislike solutions where the client infers semantics from "special" records, so I would not choose your option 1.

Your option 3 seems to record exactly what we mean and as you've shown, it extends to many such negative responses.

One issue: the database is open to being contradictory. The database could contain two records for a person one saying "I know song A" and one saying "I know no songs"; this should not happen but preventing it happening is a whole load of hassle.