Help with Database Design

[Edgars Ankorins]

Hello! I am rather new at programming and designing databases, and am therefore unable to tell a search engine exactly what i mean to answer this specific question. Please if you have any referrals to anywhere that would be great. However, i was unable to find any.

Anyway so! I have a database I have created to track my games in League of Legends. In this game there are a total of 6 item slots on your champion. I want to fill these out. So my setup is:
Games table linked to a champion table by ID. Within this games table i have 6 fields for the 6 possible items there are in a game. For the items i have 3 tables (there are 3 different levels of items, so this is kinda required). NOW i want to have these three tables to be related to the 6 fields of the Games table.

Is this at all possible or am i just being a wishful thinker?

[Rabbit]

You don't actually need 3 tables for the 3 different tiers of items. I assume you want this because each higher tier of item is composed of items from the lower tiers.

You only need 2 tables, for any amount of tiers. One table to hold all the item information, and another table to hold the "ingredient s" of the items.

Which means that those 6 fields only need to reference the one item table. And that item table can reference the ingredients table if needed.

Normally I would recommend against storing all 6 items as separate columns. Because it leads to inflexibility in querying. For example, if they add one more item slot, it means a change in almost all the queries you build. But in this particular case, I doubt they will be making any such change.

[NeoPa]

As all the ingredients are also items you only need a single item table. The level of each item is an attribute and not a reason for a different structure.

Champions, just like the items, are a finite list from which to choose and link to.

The only table you need to reflect your games is the Game table. This would include links to the champion used for that game as well as six links to the items you ended up with in that game. It would make sense to have a GameItem table instead of Item attribute links in your Game table, but it's nevertheless possible to keep these links in the Game table itself.

NB. The Item table should include fields to reflect which other items, if any, are required to produce the item, as well as the cost.

[Rabbit]

I imagine a pared down version looking something along the lines of

Game Table

GameID     PK
GameDate   Date
ChampionID Integer

GameItems Table

GameID FK
ItemID FK

Champion Table

ChampionID   PK
ChampionName Text

Item Table

ItemID   PK
ItemName Text
Cost     Integer

Ingredients Table

ItemID     FK
Ingredient FK

Again, I do think the game items should be a separate table, it's doable in one. Querying is just much easier if they're in a separate table.

[NeoPa]

My understanding from my son who plays the game (and knows how to explain the logic a bit more clearly than the OP) is that the ingredients for the items are money and, potentially, other items, so no Ingredients table required.

On the other hand, the Item table needs to include cost in gold as well as links to any other items which are used to create it.

[Rabbit]

Well, each item is composed of 1-3 lower tier items. And there are 3 tiers. So in all, a single tier 3 item can be composed to 2-6 items. Which is why I suggested a separate ingredients table.

[NeoPa]

I can understand why many might suggest that, but as the components that any item is composed of are also items, why would you (Rabbit) suggest a separate table?

I know your understanding of table structure is second to very few indeed, so I suspect we have some sort of difference of understanding of what we're dealing with (particularly as it's not too clearly defined by the OP and I don't know how much you understand of the game).

[Rabbit]

Well, if I'm understanding you correctly, the table structure would look like

ItemID   Integer
ItemName Text
Cost     Integer
Ing1     Integer
Ing2     Integer
Ing3     Integer

This would require 6 joins to get the Cost and Item Names for all the ingredients. While the two table structure would require 2 joins.

[NeoPa]

See! I knew you understood better than it appeared. I hadn't appreciated you were suggesting the Ingredient table as a joining table only, to specify the links between Items. Ingredient, in your terminology then, refers to Items which are traded in to create other Items. That makes much more sense to me now I appreciate that.

BTW. Cost is in gold, so doesn't need further clarification (No JOINs required for it at all) and my understanding of the max number of Items (currently) used to create any other items is four (where any or all of those lower level items may themselves have been created using up to four further items of course). These are just details though and don't really effect the design of the table structure as such.

Now I understand your rationale better I like it of course :-)

I thought I'd covered that concept myself in one of my earlier posts (but looking back I only actually mentioned it in reference to the Game data). I didn't recognise your suggested structure for what it was when I saw it. I absolutely agree, BTW, that a similar approach for the Game data would be much better design.

[Rabbit]

Yeah, after posting that, I realized the Game Item data should be its own table. It suffers from the same drawback. As it is, it would require 6 joins times 2 joins to get all item data. Separated, it would require 4 joins, not counting the champion join.

Edit: I changed the table structure accordingly in the original post.

[Rabbit]

I just realized you would need 2 more joins to join the ingredients back to the item table to get the tier 2 and tier 1 cost and item names. Still, overall, less joins if they add more simultaneous ingredients.

[NeoPa]

I doubt the full structure of an item would be required in any report. The items that go to make up the particular item should be fine. Each of those, and their ingredients, would be included elsewhere in the report.