CODEWITHSUNDEEP
database-designdata-modeling
learningtech
learningtech

Reputation: 33705

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 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?

Upvotes: 4

Views: 20937

Answers (5)

RDeckard
RDeckard

Reputation: 21

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.

Upvotes: 2

Etamar Laron
Etamar Laron

Reputation: 1162

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.

Upvotes: 0

Venki
Venki

Reputation: 2169

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.

Upvotes: 3

tvanfosson
tvanfosson

Reputation: 532515

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 0

Related Questions