Search code examples
mysqldatabasemany-to-manyrelationship

Many to Many Relationship over multiple tables

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.
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")

Situation:
There are buildings/researches which are all realized over the database(to prevent a lot of hardcoding)

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;

Some of those buildings and researches have a tech tree of course.
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:
Researches also can have buildings/other researches as a requirement.
So I started to create "many-to-many"-resolving groups like this one:

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;

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.
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.

Solution

  • This is a pretty open ended question, but here are some thoughts:

    • You have research items, buildings, and units. All of these share the ability to be 'researched/built' I assume (cost, time).
    • You have a concept of prerequisites. You must have done research X / built building Y before building unit Z.

    I think the design which might be the cleanest would be a table that contains all 'buildables', including research items. This might just be a cost, a builder (a link to the buildables table), a name, and an ID.

    You can add an extender table on top of this. You make a 'structure' table which references one 'buildable', and adds any extra fields 'structure' needs.

    Finally, you can make a 'prerequisite' table. This has two buildable IDs, one for the buildable in question, one for the required item. Now you don't have separate requisite tables for structToResearch, structToStruct, researchToStruct and researchToResearch.

    TL:DR; Make a base table that the many to many relationship meaningfully applies to. Make per type specific tables that hold a foreign key to the base table.