title: Many to Many Relationship over multiple tables
body: <p>I have a problem with my database design. I am making a browsergame as a small project to help improve my overall understanding of websites and database design.<br>
  Of course I wouldn't post here if I wouldn't have tried to find the solution myself(It is one of those things where searching the question it finds other questions, like the "how to create a many 2 many table")  </p>
  
  <p>Situation:<br>
  There are buildings/researches which are all realized over the database(to prevent a lot of hardcoding)</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `structType` (
   `idStructType` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    `idRace` int(11) NOT NULL,
    UNIQUE (name),
    FOREIGN KEY (idRace) REFERENCES race(idRace),
    PRIMARY KEY (idStructType)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Some of those buildings and researches have a tech tree of course.<br>
  Here's where my problem starts: A Structure can have multiple structures as a requirement as well as multiple researches.
  It also goes the other way around:<br>
  Researches also can have buildings/other researches as a requirement.<br>
  So I started to create "many-to-many"-resolving groups like this one:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `structtypeReqStructtype` (
  `idStructType` int(11) NOT NULL,
  `idStructType_required` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  UNIQUE (idStructType, idStructType_required, level),
  FOREIGN KEY (idStructType) REFERENCES structType(idStructType),
  FOREIGN KEY (idStructType_required) REFERENCES structType(idStructType)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>This in the end leaves me with 4 tables only for the techtree between structures and researches, let alone ships and defenses and it feels like bad design to me.<br>
  There almost has to be a better way to do this. Would be very glad to get a little help in which direction I should take the thought process.</p>