建立零或一对多关系模型

发布于 08-27 07:51 字数 169 浏览 8 评论 0原文

我应该如何在数据库中建模零或一对多关系?例如,用户记录可能有也可能没有父记录。那么我的用户表应该有一个 t_user.parent_id 还是应该有一个名为 t_user_hierarchy 的关联表,其中包含 t_user_hierarchy.parent_id 和 t_user_hierarchy.user_id 列?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

爱给你人给你2024-09-03 07:51:47

看看这个线程。类似的问题:任何必要的可为空外键的示例?

是否有另一个关联表或可为空的外键取决于您的用例。它是主观的,取决于您的设计。

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.

扛起拖把扫天下2024-09-03 07:51:47

1NF 规定不可为空的列。因此,要实现零到一的关系,请在子表中放置一个指向父表的外键(假设该表可能有也可能没有与父表相关的条目)。然后使用从父级到子级的外连接查询来检索有父级和没有子级的实例。

示例:

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)

这将返回所有客户记录并关联所做的任何订单。它还会返回没有订单的客户。

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.

调妓2024-09-03 07:51:47

只需将子表(用户表?)中的外键设为可为空即可

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

相守太难2024-09-03 07:51:47

我会选择一个可以为空的父 ID。然后,您可以使用自连接来获取任何特定记录的父级或子级。

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.

纵情客2024-09-03 07:51:47

数据库中的零或一对多关系通常通过声明字段(在您的实例中为Parent_ID)来表示,然后保留一个值来表示它指向任何内容。

由于关系数据库 (RDBMS) 通常允许使用 NULL 值,因此您可以使用 NULL 值来标记没有父记录的特定记录。

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

上面的示例适用于 Oracle RDBMS,但其他数据库的想法也类似。或者,您可以使用一个字段专门标记数据库来说明这一点,但这通常是一种重载,因为大多数数据库系统可以处理 NULL 的特殊情况,而无需添加其他字段。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文