数据库模型结构 [英] database model structure

问题描述

我有一个列组。 群组具有不同类型存储在group_types（买方，卖方，裁判）。只有当该组是买方类型时，它才具有另一种类型（更专业），例如电气和机械。

I have a column groups. Groups has different type stored in group_types (buyers, sellers, referee). Only when the group is of type buyer it has another type (more specialized) like electrical and mechanical.

我有点困惑，如何将它存储在数据库。

I'm a bit puzzled with how I will store this in a database.

有人可以建议我一个数据库结构？

Someone can suggest me a database structure?

感谢

推荐答案

将 group_types 存储为一个非统计表（具有嵌套集或 parent-child 模型）：

Store your group_types as a hieararchical table (with nested sets or parent-child model):

父子：

typeid parent name

1      0      Buyers
2      0      Sellers
3      0      Referee
4      1      Electrical
5      1      Mechanic

SELECT  *
FROM    mytable
WHERE   group IN
        (
        SELECT  typeid
        FROM    group_types
        START WITH
                typeid = 1
        CONNECT BY
                parent = PRIOR typeid
        )

将选择 Oracle 。

嵌套集：

typeid lower  upper  Name
1      1      2      Buyers
2      3      3      Sellers
3      4      4      Referee
4      1      1      Electrical
5      2      2      Mechanic

SELECT  *
FROM    group_types
JOIN    mytable
ON      group BETWEEN lower AND upper
WHERE   typeid = 1

将选择任何数据库中的所有买方。

will select all buyers in any database.

嵌套集可在任何地方实施，需要对 group_types 进行分层排序或频繁更新。

Nested sets is implementable anywhere and more performant, if you don't need hierarchical ordering or frequent updates on group_types.

父子可以在 Oracle 和 SQL Server 中轻松实现，并在 。

Parent-child is implementable easily in Oracle and SQL Server and with a little effort in MySQL. It allow easy structure changing and hierarchical ordering.

请参阅我的博客中关于如何在 MySQL中实现它的文章 ：

See this article in my blog on how to implement it in MySQL:

• MySQL 中的分层查询

• Hierarchical queries in MySQL

这篇关于数据库模型结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！