Saving sets of itemstacks in SQL

database-design

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:

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

Best Answer

The maps table looks pretty ok. The name should probably be unique so you have the possability to skip mapid. Also, I would use the identifier mapname instead of just name which is to vauge, and also highly likely to be a reserved word (I have not checked though). Alternative design:

CREATE TABLE maps
( mapname varchar(64) not null primary key
, max_no_players int not null
, min_no_players int not null
);

You may or may not introduce a surrogate key, but then the name should still be unique (my guess):

CREATE TABLE maps
( mapid int not null primary key 
, mapname varchar(64) not null unique
, max_no_players int not null
, min_no_players int not null
);

Next, I would look at CHESTS. A chest seem to have no attributes, so I will just add a surrogate key:

CREATE TABLE chests
( chestid int not null primary key 
);

A map can contain one or more chests:

CREATE TABLE chests_in_maps
( chestid int not null
, mapid int not null
,     constraint pk_chests_in_maps 
          primary key (chestid, mapid)
,     constraint ...
          foreign key (chestid)
          references chests (chestid)
, <f.k. for maps>
);

I would then continue with ITEMS and ITEMS_IN_CHESTS in a similar way. Properties for ITEMS looks like it is attributes of an ITEM.

This is by no means a complete answer, but it hopefully gives you some ideas.

Related Solutions

Mysql – How to structure a database to show data for each USA State and Canada Territory on a Per user basis

Well, I guess I will have to make assumptions about what your user and campaign data look like.

Maybe a schema like this:


Users
-----
  id (PK)

Campaigns
---------
  id (PK)
  user_id (This it the "user owns a campaign" relation, FK to users.ID)
  (other fields)

territories
-----------
  id (PK)
  name
  country

territories_campaigns
---------------------
  id (PK)
  territory_id (FK to territories.id)
  campaign_id (FK campaigns.id)

You use the territories_campaigns table to determine which campaign is happening in which territory. The structure of this table allows a campaign to span multiple territories, and each territory can host multiple campaigns.

Since the campaign table refers to the user who owns it, to build a map for a specific user, make sure you filter on the user_id

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

There is a collection of buckets which may contain at most 1 item from each category.

A unique constraint on bucket_id and category_id in items.

Best Answer

Related Solutions

Mysql – How to structure a database to show data for each USA State and Canada Territory on a Per user basis

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Related Question