帮助树状结构
我有一些财务数据需要存储和操作。假设我有 2 个部门，在 2 个城市设有办事处，使用 2 种货币和 4 个银行账户。 （实际上比这更复杂。）我想显示一个这样的列表：
Electronics
Chicago
Dollars
Account 2 -> transactions in acct2 in $ in chicago/electronics
Euros
Account 1 -> transactions in acct1 in E in chicago/electronics
Account 3 -> etc.
Account 4
Brussles
Dollars
Account 1
Euros
Account 3
Account 4
Dessert Toppings
Chicago
Dollars
Account 1
Account 4
Euros
Account 2
Account 4
Brussles
Dollars
Account 2
Euros
Account 3
Account 4
因此，在除顶部之外的每个级别，类别可以出现在多个位置。我一直在阅读about 关于各种方法，但这些示例似乎都没有解决我的特定用例，其中节点可以出现在多个层次结构中的一个位置。 （也许有一个与“树”或“层次结构”不同的名称。）
我想我的层次结构实际上类似于“Division”>“hierarchy”。城市>货币“电子”和“欧元”只是每个级别的实例，但我不太确定这有什么帮助或伤害。
一些注意事项：这是一个演示站点，因此数据集不会很大——易于设置和维护比查询效率更重要。 （我实际上正在考虑手动构建一个数据对象，尽管我更愿意以正确的方式进行。）此外，FWIW，我们正在使用 ms 访问后台的 php 进行工作 -最后，因此任何在该环境中使这变得容易的库都会有所帮助。 （我已经找到了嵌套集合模式的几个实现。）
I've got some financial data to store and manipulate. Let's say I have 2 divisions, with offices in 2 cities, in 2 currencies, and 4 bank accounts. (It's actually more complex than that.) I want to show a list like this:
Electronics
Chicago
Dollars
Account 2 -> transactions in acct2 in $ in chicago/electronics
Euros
Account 1 -> transactions in acct1 in E in chicago/electronics
Account 3 -> etc.
Account 4
Brussles
Dollars
Account 1
Euros
Account 3
Account 4
Dessert Toppings
Chicago
Dollars
Account 1
Account 4
Euros
Account 2
Account 4
Brussles
Dollars
Account 2
Euros
Account 3
Account 4
So at each level except the top, the category can appear in multiple places. I've been reading around about the various methods, but none of the examples seem to address my particular use case, where nodes can appear in more than one place in the hierarchy. (Maybe there's a different name for this than "tree" or "hierarchy".)
I guess my hierarchy is actually something like Division > City > Currency with 'Electronics' and 'Euros' merely instances of each level, but I'm not quite sure how that helps or hurts.
A few notes: this is for a demo site, so the dataset won't be large -- ease of set-up and maintenance is more important than query efficiency. (I'm actually considering just building a data object by hand, though I'd much rather do it the right way.) Also, FWIW, we're working in php with an ms access back-end, so any libraries out there that make this easy in that environment would be helpful. (I've found a couple of implementations of the nested set pattern already.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您确定要为此使用分层设计吗？对我来说，层次结构似乎更多是所需输出格式的结果，而不是数据结构固有的东西。
如果您必须以不同的顺序显示数据，例如“城市”>“城市”，该怎么办？货币>分配？这样岂不是很麻烦吗？
您可以改用一种简单的结构，其中一张表用于分支机构，一张表用于城市，一张表用于货币，然后一张帐户表，其中 Branch_ID、City_ID 和Currency_ID 作为外键。
Are you sure you want to use a hierarchical design for this? To me, the hierarchy seems more a consequence of the desired output format than something intrinsic to your data structure.
And what if you have to display the data in a different order, like City > Currency > Division? Wouldn't that be very cumbersome?
You could use a plain structure instead, with a table for Branches, one for Cities, one for Currencies, and then then one Account table with Branch_ID, City_ID, and Currency_ID as foreign keys.
我不确定你正在使用什么数据库平台。但如果您使用的是 MS SQL Server，那么您应该查看使用公用表表达式的递归查询 (CTE)。它们易于使用，并且专为您所说明的情况类型（例如材料清单）而设计。查看此网站了解更多详细信息：http://www.mssqltips.com/tip。 asp?tip=1520
祝你好运！
I'm not sure what database platform you're using. But if you're using MS SQL Server, then you should check out recursive queries using common table expressions (CTEs). They're easy to use and are designed for exactly the type of situation you've illustrated (a bill of materials, for instance). Check out this website for more detail: http://www.mssqltips.com/tip.asp?tip=1520
Good luck!