A relationship model of zero or one to another

Question

How should I simulate the ratio of zero or one to a large number in a database? For example, a user record may or may not have a parent. So, should my user table have t_user.parent_id or should I have an associative table named t_user_hierarchy with columns t_user_hierarchy.parent_id and t_user_hierarchy.user_id?

Answer 1

Take a look at this topic. Similar problem: Any example of the desired foreign key?

Whether you should have a different association table or a null foreign key depends on your use case. Its subjectivity also depends on your design.

Answer 2

1NF does not contain null columns. Therefore, to implement a one-to-one relationship, put the foreign key in the child element (provided that it is a table that may or may not contain a record associated with the parent) that points to the parent. Then use an external connection request from parent to child to get 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 link any order made. He will also return Customers who did not have orders.

Answer 3

( ?)

Answer 4

id. self-, - .

Answer 5

, Parent_ID, .

(RDBMS) NULL, NULL , .

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. Alternatively, you can specifically mark the database with a field by specifying this, but this is usually an overload, since most database systems can handle the special NULL case without adding an additional field.