7

I am creating a table to store information about the placement of objects in a grid (game map).

For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, (3,3) has a red chair and so on.

There can be multiple items in any square, but the same item shouldn't be in the same square twice.

is it acceptable to have this structure? (all columns in primary key)

 (room_id (FK), room_object_id (FK), x_cell, y_cell)(PK)

or will it be better to have this

id(PK), room_id (FK), room_object_id (FK), x_cell, y_cell

I think the first way makes more sense, but I'm worried that increasing the integrity checks whenever something is entered might create a lot of unnecessary overhead.

Roger Smith
  • 73
  • 1
  • 3

2 Answers2

6

First off, don't worry about performance until you're sure you have a performance problem, especially for a tiny number of rows.

Second, whether you use a unique index that is primary key or a unique index that isn't the primary key to enforce the uniqueness of the combination of your four columns the performance will be the same with respect to that index.

Whether to make that unique index the primary key should depend on what else is going on in your schema. If you have another table (or tables) that reference this table, then your compound primary key is going to get propagated to other tables as a foreign key. If that is going to happen, they you have more to think about.

If you don't need to worry about propagating a compound foreign key, then you should probably just make your compound unique index the primary key of your table. That would be the least effort which achieves your goal of enforcing your business rule.

Joel Brown
  • 12,604
  • 2
  • 32
  • 46
0

Number one is representing the desired pk and can enforce the desired behavior.
If you'd use number two you would have to add a unique key on room_id , room_object_id , x_cell, y_cell .
IMHO you should use number one.

bummi
  • 713
  • 1
  • 8
  • 16