Relational Database Design With MySQL

Question

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?

David Fells · Accepted Answer · 2011-05-05 15:37:12Z

1

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?

answered May 5, 2011 at 15:37

David Fells

6,8081 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Relational Database Design With MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related