您好，我想知道如何在表SQL中创建子节点的无限子节点，等等 [英] Hello, I want to know how to make infinite child nodes of child nodes and so on in table SQL for chart of accounts

问题描述

我们公司设计的会计系统具有无限级别的会计科目这些数字并不是唯一的例如



1000 /资产



1100 /流动资产

所以我需要制作

L1 L2 L3 L4 L5等等......无限级别
1100/100/200/300/100



但另外还有100,200,300类别的负债例如



2000负债

2100短期负债



L1 L2 L3 L4 L5

SO Theres帐户喜欢这个：2100/100/200/300/100





最后，我尝试了什么要做的是设计这个会计表这样：



表名：帐号

[AccountID，ParentID，Level，Name]我猜我make AccountID，ParentID，Level CompositeKey



问题是我如何知道100继承自300继承它来自200和来自负债2100？它也将来自父母1100



那个问题也许我不能设计好这个，



我知道我可以使用Levels As String Not Integer但是大数据和大记录将使查询变慢例如内部帐户表ParentID可以是2100/100/200/300

这可能会起作用，但是我如何使用整数值和孩子的无限孩子来设计它们





另一个附加问题：我如何将其附加到TreeView DataSource！





很抱歉，如果我让你们误会或者说不好。



我尝试了什么：



设计ID，ParentID的正常表，但我实际上是失败。

Our company designing Accounting Systems which Have Chart of Accounts Of Infinite Levels And these numbers is not unique For Example

1000 / Assets

1100 / Current Assets
So I Need to make
L1 L2 L3 L4 L5 And So on.. Of infinite Levels
1100/100/200/300/100

But There`s Also 100, 200 ,300 of Another Category Called Liabilities For Example

2000 Liabilities
2100 short term liabilities

L1 L2 L3 L4 L5
SO Theres Account Like this : 2100/100/200/300/100


Finally, What i try to do is designing this accounting Table Like This :

Table Name : Account
[AccountID, ParentID, Level, Name] I Guess i make AccountID, ParentID,Level CompositeKey

The Question Is How I Know that 100 inherit from 300 inherit from 200 And Came From Liabilities 2100 ? it will also came from Parent 1100

That`s Problem maybe i cant design this well,

I know i can use Levels As String Not Integer But Big Data and big records will make queries slow For Example Inside Account Table ParentID Can Be 2100/100/200/300
And This maybe will work, but how i design these with Integer Values And Infinite Child Of Child


Another Additional Question : How I Attach This To TreeView DataSource !


Sorry, if i make you guys misunderstanding or can`t explain it well.

What I have tried:

Desiging Normal Table Of ID, ParentID, but i actually Fail.

推荐答案

也许你可以使用多对多关系，这是一个例子：

数据建模 - 多对多关系[Gerardnico] [< a href =https://gerardnico.com/wiki/data_modeling/many-to-manytarget =_ blanktitle =New Window> ^ ]



我选择的数据库（经过SQL Server多年的努力）现在是 PostgreSQL ，它支持多值字段，参见：PostgreSQL：文档：9.5：复合类型 [ ^ ]



但是你可能正在使用SQL Server，可能 XML字段将是一个选项，请参阅此示例：使用FOR XML子句将查询结果作为XML返回 - 简单对话 [ ^ ]

Maybe you can use a many-to-many relationship, here is an example:
Data Modeling - Many-to-many Relationship [Gerardnico][^]

My database of choice (after struggling with SQL Server for years) is now PostgreSQL, it supports "multi value" fields, see: PostgreSQL: Documentation: 9.5: Composite Types[^]

But as you probably are using SQL Server, maybe XML fields would be an option, see this example: Using the FOR XML Clause to Return Query Results as XML - Simple Talk[^]

这篇关于您好，我想知道如何在表SQL中创建子节点的无限子节点，等等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！