Model a Zero or One to Many Relationship

How should I model a zero or one to a many relationship in the database? For example, a user record may or may not h开发者_StackOverflow社区ave 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?

Look at this thread. Similar problem : 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.

1NF stipulates no nullable columns. Therefore to implement a zero-to-one relationship, place a foreign key in the child (assuming this is the table that may or may not have an entry related to the parent) that points to the parent. Then use an outer join query from the parent to child to retrieve instances where there are parents with and without children.

Example:

Customer Table (i.e., parent)
   CID (Primary Key)
   Customer_Name
   Customer_Address
   ...

Order Table (i.e., child)
   OID (Primary Key)
   Ordered_Date
   Order_Quantity
   ... (product ordered would be a foreign key to the Products table; not relevant to discussion)
   CID (Foreign Key to Customer table)

SQL:
   SELECT Customer.Customer_Name, Order.Ordered_Date, Order.Order_Quantity 
   FROM Customer 
   LEFT OUTER JOIN Order 
   ON Customer.CID = Order.CID (syntax generic)

This will return ALL Customer records and associate ANY Order made. It would also return Customers that had NO orders.

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

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.

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.