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;

• Ewan: meta_data varchar(255) NOT NULL DEFAULT '',
• Andrew: Aren't you already keeping a consistent ID in furniture_item_id? Or does that represent a class of items, like a variety of chair?
• VoiD HD: furniture_item_id is the base furniture item (table, chair, whatever) the item record represents.
• VoiD HD: @Ewan meta_data is occupied for other things otherwise this would be n okay idea, but then what would it match to? It needs some considering.
• Ewan: if you want an exact solution you will have to explain the exact steps and how a teleporter purchase works. But, yeah, just put a json doc in metadata and store whatever you want

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.

• LoztInSpace: This is almost what I would do. I'd a) call it "furniture" so it contains all items regardless of ownership or placement, b) Make player_Id NULLable so player is optional (i.e. it's in a room) and finally c) put a constraint on player_id and placement_id so only one of them can be NULL at a given time (not neither, not both).
• VoiD HD: Thanks! I've accepted your answer. I'm going to do what you said, drop room_furniture_items, make something like player_furniture_item_placement_data and reference that, null if not placed anywhere, thank you!

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;