解决多对一、子表在父表之前的问题
在我的程序中，我需要制作一个组织结构图，从个人开始并将他们分组到经理下，然后将这些经理分组到高级经理下等等......我如何在数据库方面做到这一点？这是一种多对一的关系 - 我认为这是非法的且不可能的 - 并且就像从子表 - 个人 - 在创建其父表 - 经理之前开始。 如果有人可以帮助我指明设计数据库的正确方向，我将非常感激！
----编辑---- 下面是对我的问题的更详细的解释：
假设我的程序的用户首先输入了 35 个他们想要在他们将要从事的项目中使用的个人。现在，可以创建几个可能的管理职位，并将员工/其他（较低）经理分配给这些职位。用户不必创建任何特定位置，但可以根据自己的喜好挑选和分配。唯一的限制是总会有一位高级经理 - 让我们称他为总裁 - 并且他应该（但不必）只向任何特定经理（包括总裁）分配 5-10 人。因此，在本例中，我们从 35 个人和一名总统开始。现在我们开始将这 35 个人分组并指定其中一人作为经理。假设用户决定组建两支 6 人团队、两支 9 人团队和一支 5 人团队 - 每个组都有一名单独成员被指定为该团队的经理。现在我们有 5 个团队，每个团队都有自己的经理，这五名经理现在将分配给总统。程序会放过这个，但警告用户最后一个团队中经理手下的人数少于 5 人（只有 4 人）。因此，用户现在（或随时）可以返回并更改组织。假设他想解决这个问题并更换团队。于是，他将两队 9 人中的一队拆散，并指派一名人员到缺人的队伍中。这使得最终计数为 3 个 6 人团队（5 名个人成员和 1 名经理）、1 支 9 人团队（8 名 ind 和 1 名经理）和 1 支 8 人团队（7 名 ind 和 1 名经理），并且分配了所有 5 名团队经理给总统。
只有在现实生活中，这种情况才能进一步分解——嵌套更多级别的管理。我该怎么做？
下面乔·霍普夫加特纳的回答仍然有效吗？我可以只创建一个“角色”列，即使稍后会分配该角色（在最初创建行之后）？还是随时改变？这个自引用外键可以为空吗？ （就像尚未分配经理的情况一样）或者我应该在将个人分配到团队后将其移至单独的“团队”表，并在将经理分配给团队后将其移至“经理”表已被指定为经理，然后为引用经理表的团队表添加外键？
另外，我正在使用 MySQL，那么您不能使用自引用 ON UPDATE CASCADE 或 ON UPDATE SET NULL 操作这一事实是否会影响我的特定情况（因为用户无疑会经常更改分配）？
非常感谢你帮我解决这个问题！！！
In my program, I need to make an organization chart starting with individuals and grouping them under a manager, then group those managers under senior managers etc... How do I do this with regards to the database?? This is a many to one relationship - which I thought was illegal and impossible - and would be like starting with the child table - individuals - before creating its parent - manager.
If someone can help point me in the right direction to design my database I'd really appreciate it!!
----EDIT----
Here's a more detailed explanation of my problem:
Let's say a user of my program starts by entering in 35 individuals they'd like to use on some project they're going to work on. Now, there are several possible management positions that can be created and have employees/other (lower) managers assigned to those. The user doesn't HAVE TO create any particular position, but can pick and choose and assign however they like. The only constraints are there will always be a top manager - let's call him the President - and that he SHOULD (but doesn't HAVE TO) only assign 5-10 people to any given manager (president included). So in this example we are starting with 35 individuals and a President. Now we start grouping the 35 individuals into teams and assigning one of them as manager. Say the user decides to have two teams of 6, two teams of 9, and one team of 5 - each of these groups has one of its individual members assigned as manager of that team. So now we have 5 teams, each under its own manager, and those five managers would now be assigned to the President. The program would let this go but warn the user that the number of individuals under a manager in the last team is less than 5 (is only 4). So the user can now (or at ANYtime) go back and change the organization. So let's say he wants to fix this issue and changes up the teams. So he breaks up one of the two teams of 9 and assigns one individual to the team that was short a person. This makes a final count of 3 teams of 6 (5 individual members and one manager), one team of 9 (8 ind and 1 mngr), and one team of 8 (7 ind and 1 mngr) and all 5 team managers are assigned to the president.
Only in real life this can get broken down much further - nests with many more levels of management. How can I do this?
Is Joe Hopfgartner's answer below still valid? Can I just make a "role" column, even though the role would be assigned later (after the row is initially created)? Or changed at any time? Can this self-referencing foreign key be null? (as it would be in the case that no manager has been assigned yet) Or should I move the individuals to a separate "team" table after they've been assigned to a team and move the managers to a "manager" table once they've been assigned as a manager, then add a foreign key for the team table referencing the manager table?
Also, I am using MySQL, so does that fact that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations affect my particular case (since users will no doubt often be changing the assignments)?
Thank you so much for helping me with this!!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是一棵树。您可以使用带有
parent_id字段的单个表，以指示哪条记录是当前记录的父记录。It's a tree. You can use a single table with a
parent_idfield, to indicate which record is the parent to the current one.如果一个人只能拥有一名经理，只需引入“父级人员”id 字段或将经理放在单独的表中即可。
如果没有，请为类似“person_has_manager”的关系创建另一个表，将人员分配给经理。
您还可以将经理和人员放入同一个表中并引入“角色”字段，但是如果您想确保只有经理才能分配人员，那么在使用外键约束时这可能会很棘手。
为了解决这个问题，您可以引入一个“person_is_manager”表，将人员链接到他们作为经理的工作，并且可以将人员链接到由该经理管理。这看起来像这样：
if one individual can only have one manager, just introduce a "parent person" id field or put managers in a seperate table.
if not, create another table for a relation like "person_has_manager" that assigns a person to a manager.
you can also put managers and persons into the same table and introduce a "role" field, but this can be tricky when using foreign key constraints if you want to ensure that only manages can have persons assigned to them.
to solve this problem you can introduce a "person_is_manager" table that links person to their job as manager, and persons can be linked to be managed by that manager. this ould look like this: