Model a Zero or One to Many Relationship

Question

How should I model a zero or one to a many relationship in the database? For example, a user record may or may not have a parent. So should my user table have a t_user.parent_id or should I have an associative table called t_user_hierarchy with the columns t_user_hierarchy.parent_id and t_user_hierarchy.user_id?

Answer 1

Just make the foreign Key in the child table (user table?) nullable

Answer 2

I would go with a nullable parent id. Then you can just use self joins to get the parent or children of any particular record.

Answer 3

Look at this thread. Similar problem : http://stackoverflow.com/questions/925203/any-example-of-a-necessary-nullable-foreign-key

Whether to have another association table or nullable foreign key depends on your use case. Its subjective and depends on your design.

Answer 4

A zero or one-to-many relationship in a database is usually represented by declaring the field, in your instance the *Parent_ID*, and then reserving a value for denoting it points to nothing.

Since relational databases (RDBMS) usually allow you to have NULL values, you may use the value NULL to mark a specific record having no parent.

Create Table T_USER (
    User_ID     Number(9) NOT NULL ,
    Parent_ID   Number(9)
)

The above example is for Oracle RDBMS, but the idea is similar in other databases as well. Alternatively, you could specifically mark the database with a field stating this, but usually this is an overload, because most database systems can handle the special case of NULL without adding an additional field.