NULL for unassigned value?

[nick]

I have a database question and am wondering if there is a standard way
of handling it.

I have an application in which a user can assign an item to a module
(that is, indicate which module the item is a member). Also, the user
can set this to 'unassigned' if it hasn't, for example, been
determined yet of which module the item should be a member. A module
is associated with a project (that is, each project can have one or
more modules). Thus, I have:

Item (
PK ID
FK ModuleID
)

Module (
PK ID
FK ReleaseID
)

Release (
PK ID
)

The problem I'm having is dealing with a module which hasn't yet been
assigned. Should I insert a NULL in Item.ModuleID in this case? Is
there a standard way of handling this? I don't want to have a row in
module as 'unassigned', since really 'unassigned' isn't a module.

Thanks for any help.

 

[Ross Grayum[MSFT]]

I believe I would make Item.ModuleID optional (allow nulls) to account for
Items with pending Module assignments, and I would also add a boolean
attribute: "Item.IsNotAssigned" which you set to differentiate an item
that is specifically not included in a module vs. an item which is pending
assignment. I know there's not secret magic in this answer, but it will
work.

Item (
PK ID
FK ModuleID (optional)
IsNotAssigned : bool
)

Module (
PK ID
FK ReleaseID
)

Release (
PK ID
)