n：n表之间的数据库设计关系？
我在DB设计方面的技能差，我需要有关关系的一些帮助。
因此用例是：
User which can be Coach or Client.
Client can have many coaches and coaches can have many clients.
Coach can create many workouts for client.
Client can also have many workouts assigned from coach.
Workout have many sets, sets have many exercises and reps etc...
所以我在图像上创建了设计。
但是让我觉得我在做错一切的是双键。
在此表中client_has_coach_has_workout我有两个键引用n：n表，它使我感到困惑，因为当我需要导入数据时，它将始终从n：n表中都有两个键。
请任何帮助。
I have poor skills for db design and I need some help about relations setup.
So use case is:
User which can be Coach or Client.
Client can have many coaches and coaches can have many clients.
Coach can create many workouts for client.
Client can also have many workouts assigned from coach.
Workout have many sets, sets have many exercises and reps etc...
So I created design on image.
But what make me feel that I am doing everything wrong is double keys.
In this table client_has_coach_has_workout I have two keys referencing n:n table and it confuses me as when I need to import data it will always have both keys from n:n table.
Any help please.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您愿意，此设计可以正常工作，但是我将考虑逆转
Coach和客户端<代码>用户之间的链接。client_id从用户用户添加user_id作为外国密钥客户端user_id作为Coach> Coach的外键导入您总是需要查找
client_id和coach_id。要简化模型逻辑，您可以将任意
ID键列添加到client_has_coach然后client_has_has_coach_has_has_has_workout仅需要一个forefer键client> client_has_has_coach_id < /代码>链接到client_has_coach表，这迫使我们从client_has_coach中查找特定的链接记录，并帮助我们实施其存在。如果您确实使用了复合键，那么我们通常可以跳过查找
client_has_coach和假设已经存在的，但这可以导致孤立的行或场景。client_has_coach在client> client_has_coach_has_workout中没有记录。client_has_coach和client_has_has_coach_has_has_has_workout之间的完整性的模型中很好地工作。但是您的命名惯例表明，这在您的模型中不是理想的方面。无论哪种方式，在导入数据时，您都需要先查找
coach记录，然后查找client记录要检查或创建记录或在client_has_coach中创建记录，然后您将相应的行导入client_has_coach_has_workout。This design can work if you want it to, but I would consider reversing the link between
coachandclienttouser.client_idfromusercoach_idfromuseruser_idas a foreign key toclientuser_idas a foreign key tocoachUltimately yes you are correct that now there are composite keys, but this is not a problem because during importing you would always need to lookup both the
client_idand thecoach_Idanyway.To simplify the model logic, you can add an arbitrary
Idkey column toclient_has_coachthenclient_has_coach_has_workoutonly needs a single foreign keyclient_has_coach_idthat links back to theclient_has_coachtable, this forces us to lookup the specific linking record fromclient_has_coachand helps us enforce it's existence.If you do use a composite key, then we can generally skip the lookup for
client_has_coachand assume that it already exists, but this can lead to orphaned rows or scenarios where there is no record inclient_has_coachcorresponding to a combination of keys inclient_has_coach_has_workout.client_has_coachandclient_has_coach_has_workout. But your naming conventions suggests that this is not a desirable aspect in your model.Either way, on import of data you would need to first lookup the
coachrecord, then lookup theclientrecord to check for or create the record inclient_has_coach, you would then import corresponding rows intoclient_has_coach_has_workout.