/******************************************************************************/
/**/
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE tbl(
  id INTEGER AUTO_INCREMENT,
  lnk INTEGER,
  nme VARCHAR(32),
  tpe ENUM('door', 'chest', 'weapon','player') NOT NULL,
  str INTEGER,
  mag INTEGER,
  mec INTEGER,
  hp INTEGER,
  xp INTEGER,
  about TEXT,
  PRIMARY KEY(id),
  FOREIGN KEY(lnk) REFERENCES tbl(id)
);

CREATE VIEW around AS
  SELECT room.id AS roomID, room.id AS doorID, room.nme, room.tpe  from tbl AS room
  UNION
  SELECT door.lnk AS roomID, door.id AS doorID, door.nme, door.tpe  from tbl AS door;




INSERT INTO tbl (nme,lnk,tpe)
  VALUES('room1',null, 'door'),
        ('room2',1, 'door'),
        ('room3',1, 'door'),
        ('room4',1, 'door'),
        ('room5',2, 'door');
SELECT * FROM around;