I’m currently programming a minecraft survivalgames minigame network and am currently setting up a database for it. I have decided to store the possible maps for my spigot instances that run on my physical servers in a table. They will contain a reference to a chest set. Every chest set will contain an itemset, which is a selection of itemstacks, that also are stored in a table. This will make it possible to make the items in the chests be of a great variety and have them pretty much unpredictable.
So in total I now have 4 tables. The first one looks like this:
CHESTSET row has the
ID of the
CHESTSET-Table as foreign key. I do this to make every map have an associated chestset with it. The chestset-table also has a row with the
ID of it, and 5 Item-Sets, so in total you can define up to 5 different types of chests. These chests will contain random items of the itemset’s that are referenced in them (the random selection will be done in my plugin, this is only about storing the selection possibilities).
Every itemset contains up to 10 items, that the contens of an individual chest on the map will be composed of.
The properties of the items themselves will be stored in yet another table, this table gives them an id, and then all the relevant properties, like the item-type, the display name, the subid, etc etc. This table gets pretty big, so I’ll just post a dummy, which has all relevant properties.
Now here starts my problem. I’ve been able to add the
ID-field of my
CHESTSET-Table as a foreign key to my
MAPS-Table. This makes sense to me because every map will have a set of predefined chest candidates. I now planned to add the
ID-column of my
ITEMSETS-Table as foreign key to all 5 of my
ITEMSET - X-rows, but I’m somehow not able to do that.
Am I thinking in the right way? For me, the multiple tables are a possibility to store multiple values in the place of one. So I store the items in a set, which can be combined to form chest groups, which then are selected for each map. I think of the foreign key’s as a way to basically "insert" the objects of another table in there. However, a column seems to be limited to a one time use as a foreign key.
What I want is that all of my 5 row’s in my
CHESTSET-table are associated with an entry of the table
ITEMSET, and likewise the entry’s of the row’s
ITEM - x in my table
ITEMSETS with an entry of the table
CHESTITEMS. How can I achieve that, if I cant make all of them a foreign key linked to the respective
ID? Also I’m fairly new to Database design, so if this is a really dumb question, I apologize in advance.