使用 MySQL 进行关系数据库设计
我目前有 3 张桌子
Users (Id, PositionId)
MonsterInstances (Id, PositionId)
TreasureInstances (Id, PositionId)
和 1 个位置表。
Positions (Id, Coordinate, TypeId)
在我的 3 个表中，PositionId 是我的 Position 表的外键。
我想使用单个持仓表（如上所示）来标准化我的所有持仓数据。我面临的问题是我必须识别一种类型，以便当我的查询执行时，它知道要查询哪个表。
例如，
SP -- GetObjectByPosition (positionId)
IF TypeId = 1
SELECT * FROM Users JOIN... WHERE PositionId = positionId
ELSE IF TypeId = 2
SELECT * FROM MonsterInstances JOIN...
这对我来说似乎是糟糕的设计。我能想到的唯一解决办法是有 3 个单独的桌子。
UserPositions
MonsterInstancePositions
TreasureInstancePositions
然而，我并不总是对提取用户、怪物或宝藏数据感兴趣。有时我只想要职位 ID 和位置——这意味着对于三个表，我必须进行联合。
有更好的方法吗？
I currently have 3 tables,
Users (Id, PositionId)
MonsterInstances (Id, PositionId)
TreasureInstances (Id, PositionId)
and 1 position table.
Positions (Id, Coordinate, TypeId)
PositionId, in my 3 tables, are foreign keys into my Position table.
I want to use a single Positions table, as shown above, to normalize all of my position data. The problem I am facing is that I must identify a type so that when my query executes, it knows which table to query.
e.g.
SP -- GetObjectByPosition (positionId)
IF TypeId = 1
SELECT * FROM Users JOIN... WHERE PositionId = positionId
ELSE IF TypeId = 2
SELECT * FROM MonsterInstances JOIN...
This seems like bad design to me. The only way around it I can percieve would be to have 3 seperate tables.
UserPositions
MonsterInstancePositions
TreasureInstancePositions
However, I'm not always interested in extracting user, monster, or treasure data. Sometimes I only want the position Id and location -- which would mean with three tables, I would have to do a union.
Is there a better way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
用户、MonsterInstances、TreasureInstances 可以重写为包含类型列的单个“ObjectInstances”表。然后，分别针对这 3 个表执行的查询将针对 ObjectInstances 和 typeID 执行，并引用新的 OjbectTypes 表。有道理吗？
Users, MonsterInstances, TreasureInstances could be rewritten as a single "ObjectInstances" table that includes a type column. Then queries that would work against those 3 tables separately would instead work against ObjectInstances and a typeID, referencing a new OjbectTypes table. Make sense?