简单数据库架构：优点和缺点？举例说明
我为本文的冗长、粗暴表示歉意，但我凌晨 3 点起床，担心明天必须去上班并处理这个数据库……这只是感觉不对，但也许我错了，就像事情按照我的方式完成一样...请告诉我你的想法。
我们的数据库模式看起来像这样：
page: id, label, contentid, parentpageid
content: id, xmldata
我们在页表中有大约 4000 个条目...
我已经包含了一个大大简化的页面层次结构，它看起来类似于本文底部的代码列表。
问题的根源在于数据库中充满了这样的事情：
- 对于每个带有“标签”的“页面” SET（有3个） 有16个 孩子们，每个孩子都标有“SECTION” 数字。
- 每个 SECTION 都有一个 孩子的名字是“关于”，并且 名为“Info”的子项
- 每个“关于”页面都有一组 孩子们被贴上标签：“关于事物”、“关于事物” 和“关于其他内容”
请注意，尽管孩子们的标签相同，但他们可能有不同（但相似）的内容。
考虑到数据，我对这个模式有一些主要问题，我想知道我是否合理，以及是否有一个好的解决方案或我应该阅读的东西。
主要问题是：有很多重复的条目和重复的层次结构。
编辑：另一个问题是，如果页面有子页面，则它的 contentid 会被忽略。浪费空间？
我还遇到了这样的问题：很难弄清楚我在树中的位置......我正在看什么类型的物体？
例如，任务节点需要以与其他节点不同的方式显示......并且确定该节点是否是任务的唯一方法是它的父级标签是否为“任务”（或“作业”或“待办事项”） 。我尝试使用节点的“深度”，但这不起作用，因为有时其他项目需要在相同深度上进行不同的显示。
提出的解决方案是添加“类型”字段...然后 UI 决定如何显示节点（例如，“导航”节点位于主选项卡栏（例如“关于”）中，任务类型节点位于下拉列表中侧边栏），但同样，这感觉也不对。
此外，目前没有方法对项目进行排序。到目前为止，我们很幸运，数据按照他们想要显示的正确顺序进入数据库，并且到目前为止还没有删除。
他们想添加一个“排序顺序”字段来解决这个问题。似乎需要进行大量的手动设置，特别是如果他们想要重新排序特定的菜单集...（例如，向每个“关于”节点插入一个新的子节点），尽管我猜想可以编写一个简单的脚本来做到这一点...尽管我的论点是我应该能够在一个位置更改它，然后就完成了。
我从未见过像这样设置的数据库，但他们声称，这就是所有数据库的设置方式。这就是他们的目的。我是不是疯了，或者这是一种荒谬的方式来设置具有如此多重复层次结构的任何数据库（另请记住，下面的层次结构比真实的页面层次结构具有更少的重复条目和重复树）。
老实说，我想提出一个更好的解决方案，但我不太确定那是什么，因为在真正的层次结构中有 8 种不同类型的“关于”样式节点，其中一些包含更多带有更多子节点的节点......但孩子们的标签和顺序总是采用相同的模式。我需要为每种类型的页面创建一个表吗？
我注意到这似乎也是网站的一个常见问题，它们可能有一堆页面，所有页面都有完全相同的子集......但没有办法说：好吧，我希望这个页面继承这个集合孩子们，但我希望他们都有不同的内容。现在我想将子项重新排列在一个位置并让它们全部更新。有没有一种强大、简单的方法来解决这个问题？
SET1
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
...
SECTION16
SET2
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SET3 (Exact same setup as SET2)
...
谢谢！
I apologise for the long-winded, rantyness of this but I'm up at 3am dreading having to go to work tomorrow and deal with this database... it just feels wrong but maybe I'm wrong and just like things done my way... Please tell me what you think.
Our database schema looks something like:
page: id, label, contentid, parentpageid
content: id, xmldata
We have about 4000 entries in the page table...
I have included a vastly simplified page hierarchy which looks something like the code listing at the bottom of this post.
The source of the problem is the fact the database is fraught with things like this:
- For every single 'page' with 'label'
SET (there are 3) there are 16
children, each labeled with SECTION
numbers. - Every single SECTION has a
child with the name 'About' and a
child with the name 'Info' - Every single 'About' page has a set of
children labeled: 'About Things', 'About Stuff'
and 'About Other Stuff'
Note although the children are labeled the same, they will likely have different (but similar) content.
I have a few major problems with this schema, considering the data, and I'd like to know if I'm justified, and if there's a good solution or something I should read.
The main problem is: There are a -lot- of duplicate entries and duplicate hierarchies.
edit: Another issue is, if a page has children, it's contentid is ignored. Wasted space?
I'm also having trouble with the fact that it's hard to figure out WHERE I am in the tree... what type of object am I looking at?
For example, the TASK nodes need to be displayed differently than other nodes... and the only way to figure out if the node is a task, is if it's parent's label is "Tasks" (or "Jobs" or "ToDo"). I tried using the 'depth' of the node, but this doesn't work because sometimes other items requiring a different display on the same depth.
The solution proposed was the addition of a "Type" field... and then the UI decides how to display a node (eg 'Nav' nodes go in the main tab bar (eg About) and the Task type nodes a dropdown in the sidebar), but again, this doesn't feel right either.
Also, currently there is NO method to sort the items. We've just been lucky so far that the data went into the database in the right order they want to display it, and there have been no deletions so far.
They want to add a 'sort order' field to fix this. it seems like a lot of manual work to set up, especially if they ever want to reorder a specific menu set... (eg insert a new child to every single About node), although I guess a simple script could be written do it... though my argument is that i should be able to change it in one location and then it's just done.
I've never seen a database set up like this, but they claim, this is how all databases are set up. It's what they are for. Am i going crazy or is this a ridiculous way to set up any database with this much duplicated hierarchy (also keep in mind the below hierarchy has many less duplicate entries and duplicate trees than the real page hierarchy).
Honestly, I'd like to suggest a better solution but I'm not really sure what that is because in the real hierarchy there are 8 different types of 'About' style nodes, some of which contain more nodes with more children... but the children's labels and ordering are always in the same pattern. Do I need to create a table for each of these types of pages?
I've noticed this also appears to be a common problem for websites, where they might have a bunch of pages, all with the exact same set of children... but no way to say: ok i want this page to inherit this set of children, but i want them to all have different content. now I want to rearrange the children in one location and have them all update. Is there a robust, simple way to solve this?
SET1
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
...
SECTION16
SET2
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SET3 (Exact same setup as SET2)
...
THANKS!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
关于您所说的一些事情，只有一些想法：
“提出的解决方案是添加一个“类型”字段......”
如果明显需要您的某些业务事物被“类型化”，因为某处不知何故存在这种不同“类型”的对象之间存在一些相关的区别，那么从关系数据库的角度来看，这是您希望/需要设置多个表（每种类型一个）的关键指示，至少在逻辑级别。
如何在物理上组织它是另一回事，SQL 无法在逻辑/物理之间做出适当的区分，这是一个悲伤但真实的现实，而且由于 SQL 在该级别上的失败，这是一个更悲伤和更真实的现实，许多开发人员也没有这个级别的线索。
“此外，目前还没有方法对物品进行排序。”
请记住，在逻辑级别，关系数据库不知道任何排序的概念。订购是一个演示问题。 dbms/数据库仅涉及其物理设计特征（尤其是索引）可以用于提供客户端/用户要求的查询项目排序。设计中是否包含此类索引是物理数据库设计决策。
“他们想添加一个‘排序顺序’字段来解决这个问题。”
对我来说，听起来介于“非常愚蠢”和“完全疯狂”之间（取决于这个排序顺序字段应该包含什么）。
“但他们声称，这就是所有数据库的建立方式。这就是它们的用途。”
对我来说听起来像是根本的无知。但我只读了你的故事部分，这可能有偏见。
“下面的层次结构比真实的页面层次结构具有更少的重复条目和重复树”。
我将告诉您“数据库的用途”：它们用于注册事实的陈述/断言。数据库中的每一行都代表一个事实陈述，该事实被认为是真实的。现在重复：“如果某件事是真的，那么说两遍并不会让它变得更真实。”版权所有 EFCodd。任何数据库都不应该保存任何重复项（当然，它们的意思是相同的）。
Just a few thoughts regarding some of the things you stated :
"The solution proposed was the addition of a "Type" field... "
If there is an apparent need for some of your business things to be 'typed' because somewhere somehow there exists some relevant distinction between obejcts of such distinct "types", then from a relational database persepective that is a key indication that you want/need to set up multiple tables (one for each type), AT LEAST at the logical level.
How you organise that PHYSICALLY, is a different matter, and it is a sad but true reality that SQL fails to make a proper distinction between logical/physical, and it is an even sadder and truer reality that because of SQL's failure on that level, lots of developers don't have a clue on that level either.
"Also, currently there is NO method to sort the items."
Keep in mind that at the logical level, relational database do not know any concept of ordering. Ordering is a presentation issue. The dbms/database is only implicated in that its physical design characteristics (indexes, notably) CAN be used to provide the queried items ordering that the client/user asks for. Whether or not such indexes are included in the design is a physical database design decision.
"They want to add a 'sort order' field to fix this."
Sounds anywhere between "pretty silly" and "totally insane" to me (depending on what this sort order field is supposed to contain).
"but they claim, this is how all databases are set up. It's what they are for."
Sounds like fundamental ignorance to me. But I only read your part of the story, which is possibly biased.
"the below hierarchy has many less duplicate entries and duplicate trees than the real page hierarchy".
I'll tell you "what databases are for" : they are for registering statements/assertions of fact. Each row in a database represents a statement of fact, which is believed to be true. And now for the duplicates : "If something is true, then saying it twice will not make it any truer." Copyright E.F.Codd. No database should ever hold any duplicates (that are intended to mean the same thing, of course).
我认为 Erwin 的分析表明，你们小组的成员对于如何使用关系数据库存在很多困惑。从您的架构来看，您似乎基本上是在数据库中重新创建文件系统。这是相当愚蠢的，你的网络服务器已经有了一个。我谦虚地建议您在进一步使用您的系统之前需要重新考虑您的设计或咨询站点设计顾问。如果你继续沿着你现在正在做的方向前进，我认为该项目可能会遇到麻烦，并且可能会浪费很多金钱/时间。
我认为您对数据以及如何对其进行建模有了更好的掌握，但是您可能需要进行几次迭代，直到您基本上满足您的要求以及数据库驱动网站的需求系统。
祝您的数据库工作好运！
I think Erwin's analysis indicates that there is a whole lot of confusion amongst members of your group as to how a relational database is to be used. Judging by your schema, it seems like your'e basically re-creating a filesystem in your database. This is rather silly, your webserver already has one. I humbly suggest you need to rethink your design or consult with a site design consultant before going any further with your system. If you keep heading down the direction your'e doing right now, I think the project might be in trouble and might waste a lot of money/time.
I think you have a better grasp of the data and how it needs to be modelled, but you might have to go through a couple of iterations until you get it mostly nailed to fit your requirements and what you need out of a database-driven site system.
Best of luck on your database work!
我认为你正在用这些内容把自己打败。
如果不存在数据库的 ER 图，请绘制一个 - 您似乎已经对内容有了合理的掌握，并将其规范化。
使用此 ERD，您应该能够轻松解决上面提到的大多数问题。
I think you're beating yourself to death with the content.
If no ER-diagram of the database exists, draw one up - you seem to have a reasonable grasp of the content already, and normalize it.
Using this ERD you should be able to resolve most of the problems you mention above quite easily.
secoif 他们称其为关系数据库是有原因的。如果没有多个表并使用联接，那么使用数据库还有什么意义，还不如使用平面文件。
您所谓的“其他员工”可以随心所欲地指出和嘲笑，但数据库驱动的应用程序的真正力量和有效性是当您开始使用联接和规范化数据时。
secoif there is a reason why they call it relational database. Without multiple tables and using joins then what is the point of using a database, might as well use a flat file.
Your so called "other employees" can point and laugh all they want but the true power and effectiveness of a database driven application is when you begin using joins and normalizing your data.
您可以像这样创建表格：
MenuItem: Id, Name
菜单项内容：ID，内容
MenuItemHierarchy：ParentMenuItemId、MenuItemId、ContentId、SortKey
通常情况下，我会这样做：
MenuItem：Id、Name、ContentId
菜单项内容：ID，内容
MenuItemHierarchy：ParentMenuItemId、MenuItemId、SortKey
并且不会重新使用我的菜单项来显示不同的内容。
You could create your tables like so:
MenuItem: Id, Name
MenuItemContent: Id, Content
MenuItemHierarchy: ParentMenuItemId, MenuItemId, ContentId, SortKey
Normally, though, I do:
MenuItem: Id, Name, ContentId
MenuItemContent: Id, Content
MenuItemHierarchy: ParentMenuItemId, MenuItemId, SortKey
And not re-use my menu items to display different content.