How to structure, would this be Many-to-Many?

[Tony Diaz]

The tables.

Items::ID
Items::Item
Items::Publisher
Items::Date
 

The Genre data is already arranged like this:

Genre::ID (Unique Key)
Genre::Name
Genre::Category_ID
Genre::Category

The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
Platforms::ID
Platforms::Name

An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.

This one has just one sub-option per category.

Example 2:  The Gameplay Genre Category has two sub-options related to it.



Example Genre Table content:

 

Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

Would each of those Genre Categories be portals showing only their related category ID?

I presume that I would add fields to the Items::Table so I can pull related records:
Items::Platform_IDfk
Items::Genre_IDfk
Items::Category_IDfk
But those only support one relationship.
Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?

 

[comment]

A long question, not sure where the focus is.

In general, if an item can belong to multiple groups, and a group can have more than one item, then the  relationship is many-to-many.

Two quick rules-of-thumb:

1. Do you have something to record about a join (e.g. the date of joining)?
2. Do you want to be able to produce a report of items by group?

If you answered yes to either one of these, you need a join table. Otherwise you may get by using a checkbox to assign an item to groups (or vice-versa - but not both).

 

3 hours ago, Tony Diaz said:

The Genre data is already arranged like this:

Genre::ID (Unique Key)
Genre::Name
Genre::Category_ID
Genre::Category

That makes no sense. Either use a value list for the categories, or put them in a table of their own. In the first case, you don't need a Category_ID. In the second, you don't need a Category field in the Genres table.

 

Edited June 20, 2020 by comment

[Tony Diaz]

Okay, it makes no sense then. I couldn't work out how it would, I just wanted to make absolutely sure there wasn't some trick voodoo way I didn't know about. That's the way the data is given according to the API docs, as I'm trying to build a local version of their classifications.

I don't care about being able to report on records by group. The individual categories are fine. So it sounds like I should break that stuff up into value lists based on those Genre Categories and go with that.

Hmm... unless I can make a value list from records using an additional indicator. e.g.: This value list (Genre_ID, Genre_Name) consists of all records from that table that are Category_ID XX... 

Otherwise time to read about Join Tables.

[comment]

I am afraid I cannot advise you on that because I don't know what is the purpose of your solution. Database design rules come after business rules. I know nothing about games. Looking at the list of categories and genres, I would have thought that each category should be a field in a Games table and that the associated genres are no more than the allowable values for such field. But that's just a guess.