Linking records through multiple tables?

Question

I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of its own.

Currently I store these records separately, in two tables; player_furniture_items and room_furniture_items.

My problem is, I have a specific condition when players purchase specific furniture items, it needs to retain information linking them together, this is only true for specific furniture item types such as teleports.

But of course when it turns from a player furniture item into a room furniture item (when its placed from player inventory to room) the ID changes.

I have a few options, I can keep track of the ID changes during the transition through something like player_furniture_item_links and room_furniture_item_links, or I can merge them into one table.

They both have their pros and cons but here are some import points....

1. If I merge the tables, rows have obsolete columns such as X Y Z coords of its placement, it doesn't care if its just sitting in a players inventory.
2. If I track through link tables, it means a lot of querying and transitioning of data just to move these entities around, feels heavy.

Tables look like this:

CREATE TABLE `room_furniture_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `room_id` bigint(20) unsigned NOT NULL,
  `owner_id` bigint(20) unsigned NOT NULL,
  `owner_username` varchar(255) NOT NULL,
  `furniture_item_id` bigint(20) unsigned NOT NULL,
  `position_x` int(11) NOT NULL DEFAULT 0,
  `position_y` int(11) NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `wall_position` varchar(255) DEFAULT NULL,
  `direction` int(11) NOT NULL DEFAULT 0,
  `limited_data` varchar(255) NOT NULL DEFAULT '',
  `meta_data` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `room_furniture_items_owner_id_foreign` (`owner_id`),
  KEY `room_furniture_items_room_id_foreign` (`room_id`),
  KEY `room_furniture_items_furniture_item_id_foreign` (`furniture_item_id`),
  CONSTRAINT `room_furniture_items_furniture_item_id_foreign` FOREIGN KEY (`furniture_item_id`) REFERENCES `furniture_items` (`id`),
  CONSTRAINT `room_furniture_items_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `players` (`id`),
  CONSTRAINT `room_furniture_items_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `player_furniture_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) unsigned NOT NULL,
  `furniture_item_id` bigint(20) unsigned NOT NULL,
  `limited_data` varchar(255) NOT NULL DEFAULT '',
  `meta_data` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `player_furniture_items_player_id_foreign` (`player_id`),
  KEY `player_furniture_items_furniture_item_id_foreign` (`furniture_item_id`),
  CONSTRAINT `player_furniture_items_furniture_item_id_foreign` FOREIGN KEY (`furniture_item_id`) REFERENCES `furniture_items` (`id`),
  CONSTRAINT `player_furniture_items_player_id_foreign` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer 1

Assuming the player remains the owner of a piece of furniture they place in a room, I would use a different database structure.

CREATE TABLE `owned_furniture_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) unsigned NOT NULL,
  `furniture_item_id` bigint(20) unsigned NOT NULL,
  'placement_id' bigint(20) unsigned DEFAULT NULL,
  `limited_data` varchar(255) NOT NULL DEFAULT '',
  `meta_data` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owned_furniture_items_player_id_foreign` (`player_id`),
  KEY `owned_furniture_items_furniture_item_id_foreign` (`furniture_item_id`),
  KEY `owned_furniture_items_placement_id_foreign` (`placement_id`),
  CONSTRAINT `owned_furniture_items_furniture_item_id_foreign` FOREIGN KEY (`furniture_item_id`) REFERENCES `furniture_items` (`id`),
  CONSTRAINT `owned_furniture_items_player_id_foreign` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
  CONSTRAINT `owned_furniture_items_placement_id_foreign` FOREIGN KEY (`placement_id`) REFERENCES `furniture_placement` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `furniture_placement` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `room_id` bigint(20) unsigned NOT NULL,
  `position_x` int(11) NOT NULL DEFAULT 0,
  `position_y` int(11) NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `wall_position` varchar(255) DEFAULT NULL,
  `direction` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `room_furniture_items_room_id_foreign` (`room_id`),
  CONSTRAINT `room_furniture_items_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The idea here is that when a piece of furniture is in the player's inventory, then the placement_id foreign key is NULL and otherwise it points to the furniture_placement entry that corresponds to where the piece of furniture has been put in a room.

An similar alternative is to make furniture_placement a link table with additional attributes between owned_furniture_items and rooms. Which works best for you depends mostly on the kinds of queries you expect to run most often.

Answer 2

Here is what I ended up doing

CREATE TABLE `player_furniture_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) unsigned NOT NULL,
  `furniture_item_id` bigint(20) unsigned NOT NULL,
  `limited_data` varchar(255) NOT NULL DEFAULT '',
  `meta_data` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `player_furniture_items_player_id_foreign` (`player_id`),
  KEY `player_furniture_items_furniture_item_id_foreign` (`furniture_item_id`),
  CONSTRAINT `player_furniture_items_furniture_item_id_foreign` FOREIGN KEY (`furniture_item_id`) REFERENCES `furniture_items` (`id`),
  CONSTRAINT `player_furniture_items_player_id_foreign` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `player_furniture_item_placement_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `room_id` bigint(20) unsigned NOT NULL,
  `player_furniture_item_id` bigint(20) unsigned NOT NULL UNIQUE,
  `position_x` int(11) NOT NULL DEFAULT 0,
  `position_y` int(11) NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `wall_position` varchar(255) DEFAULT NULL,
  `direction` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `room_furniture_items_room_id_foreign` (`room_id`),
  KEY `room_furniture_items_furniture_item_id_foreign` (`furniture_item_id`),
  CONSTRAINT `room_furniture_items_furniture_item_id_foreign` FOREIGN KEY (`furniture_item_id`) REFERENCES `furniture_items` (`id`),
  CONSTRAINT `room_furniture_items_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;