Building your world - part five: More rooms, and how to get to them

More Room! More Room!

So we now have a room, which is great, but will get a bit boring after a while.

So first we add a couple of new rooms:

insert into rooms(name,description) values('Dining Hall','A room containing pale Scandinavian furniture');
insert into rooms(name,description) values('Ballroom','A room whose floor is covered with brightly coloured balls');

mysql> select * from rooms;+--------+-------------+-----------------------------------------------------------+| roomID | name | description |+--------+-------------+-----------------------------------------------------------+| 1 | Kitchen | A bright shiny IKEA kitchen || 2 | Dining Hall | A room containing pale Scandinavian furniture || 3 | Ballroom | A room whose floor is covered in brightly coloured balls. |+--------+-------------+-----------------------------------------------------------+3 rows in set (0.00 sec)


Now we need some connection between them.

This will require another table, which I will call links.

It would be possible to have a single link between rooms so:
Kitchen <=>Dining Hall
Dining Hall<=>Ballroom

But that makes finding the links to a room more complicated, in the above example you would need to search both the left and right columns to find all the links from the Dining Hall.

It also makes the links bi-directional, and we might want to have a portal from one room to another that does not  have a corresponding return route.

So we have a source room and a destination room.

We also need to have a direction for the link, is it to the south, north, west or east? It could be even up and down (but that will be for a later iteration).

We can also have a description of the door, hole, magic mirror etc.

So the table will have the following fields:

• A link ID (an auto incrementing integer)
• A sourceRoomID containing the roomID
• A destinationRoomID containing the roomID that the link connects
• The direction (east, west, north, south), up to five characters
• A description of the link (door, hole etc.), up to twenty characters.

Now we do not want to add links between non existent rooms, so we need to add a check that there is a matching room in the rooms table.

This is called a Foreign Key constraint, we will have two, one on the sourceRoomID and the second on the destinationRoomID.

We also do not want to have more than one link on a direction (if we had two doors to the south, which one would we go through?). For this we use the UNIQUE constraint on sourceRoomID and direction.

The SQL for this table is:

create table links(

linkID  INT NOT NULL AUTO_INCREMENT,

sourceRoomID INT NOT NULL,

destinationRoomID INT NOT NULL,

direction VARCHAR(5) NOT NULL,

description VARCHAR(20),

PRIMARY KEY(linkID),

UNIQUE(sourceRoomID,direction),

FOREIGN KEY (sourceRoomID) REFERENCES rooms(roomID),

FOREIGN KEY (destinationRoomID) REFERENCES rooms(roomID)

);

mysql> show tables;+--------------------------------------+| Tables_in_technologyisnotd$adventure |+--------------------------------------+| links || rooms |+--------------------------------------+2 rows in set (0.00 sec)

So now we need to add the links.

insert into links (sourceRoomID,destinationRoomID,direction,description)

values(1,2,'south','a plain door');

insert into links (sourceRoomID,destinationRoomID,direction,description)

values(2,1,'North','a discrete door');

insert into links (sourceRoomID,destinationRoomID,direction,description)

values(2,3,'west','a grand door');

insert into links (sourceRoomID,destinationRoomID,direction,description)

values(3,2,'east','a grand door');

mysql> select * from links;+--------+--------------+-------------------+-----------+-----------------+| linkID | sourceRoomID | destinationRoomID | direction | description |+--------+--------------+-------------------+-----------+-----------------+| 1 | 1 | 2 | south | a plain door || 2 | 2 | 1 | North | a discrete door || 3 | 2 | 3 | west | a grand door || 4 | 3 | 2 | east | a grand door |+--------+--------------+-------------------+-----------+-----------------+4 rows in set (0.00 sec)

Now we have the links, how do we display them?