CODEWITHSUNDEEP
mysqlrelational-database
George Johnston
George Johnston

Reputation: 32278

Relational Database Design With MySQL

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?

Upvotes: 0

Views: 163

Answers (1)

David Fells
David Fells

Reputation: 6798

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?

Upvotes: 1

Related Questions