Jump to content

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


Recommended Posts

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.
937740460_ScreenShot2020-06-19at22_21_05.png.bab8c252c002b00f85ee045545914444.png

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

517463043_ScreenShot2020-06-19at22_20_56.png.cb153d8ce9106db5b8d300ec0c578ace.png

Example Genre Table content:
1836564414_ScreenShot2020-06-19at22_19_17.png.82133ef303c1e62972bea51f7da420c6.png

 

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?

 

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Mafia2020
      I'm not even sure to describe under which category my specific problems fall into, I can only advance some hypothesys, so I'll just describe it and let the wisdom of others guide me.
      I have a DB with several tables, I use them to make invoices. With every invoice I input the desired price and it adds automatically 19% taxes and produces a neat invoice which I can print, these three tables are called:
      Invoices 2018
      Invoices 2019
      Invoices 2020
      Now on to my problem. I wanted to create a forth table where from every previously listed table it would calculate the total revenue of the year in question, broke down like this:
      - Total Net price paid for 2018
      - Taxes for 2018
      - Total revenue for 2018 (net+taxes)
      - Total Net price paid for 2019
      - Taxes for 2019
      - Total revenue for 2019 (net+taxes)
      - Total Net price paid for 2020
      - Taxes for 2020
      - Total revenue for 2020 (net+taxes)
       
      I am unsure how to proceed, I tried creating a forth layout and a new table but I have problems brining the summary field from each year's table into the forth table/report/layout.
    • By John Link
      I have a field named "Databases" with the following values: "CORD", "SDHS", "Y Ballet". This field is displayed as a checkbox set. I recently learned that "CORD" should have been "DSA", so I naively changed "CORD" to "DSA" in the value list but then none of the records that were checked for "CORD" were checked for "DSA", which reminded me that the values in the value list are the values of the field Databases and not labels for checkboxes. I revised the value list to again include "CORD" and its checkbox is again checked for all the records for which it was originally checked.

      I wrote a script, shown in one of the attachments, to insert "DSA" into the Databases field for each of the records that contains "CORD" in that field. When I run the script I get an error message saying "Databases" is defined to contain only specific values, and that I must enter a valid value. How can DSA not be a valid value when it is included in the value list for the field? How can I accomplish what I want to do?

      Does programming scripts in FileMaker ever get easier? I don't write many scripts, but when I do I find it surprisingly difficult to accomplish what I think would be the simplest of tasks.

      Thanks in advance for your help,
      John Link


    • By Tony Diaz
      How do I set a field with a value list item from a script, from a set of tick box values? .. or remove a value list item, but leave whatever else is there alone?
      Looks like GetValue with the name of the value list and the position # of the value, but I'm sure that also means that if I change that value list, I need to change any scripts that reference them by position # too..
      Something like detecting if a string is already there, and if not add it, surrounded by C/Rs, unless it's the first item entry?
      For the first time a field is set, I presume I could just Set Field (field ; "Value List Item"), and if there's only one entry/line in it, just clear it.  But if there's already a value there, I just want to select an additional one.
    • By stan111
      Gents,
      I use self portal on my Clients layout to serve two things:
      1. display all the records and
      2. quick navigate among them. 
      This portal is not displayed in Webdirect. 
      Is it possible to make it work?
    • By BrainlessBlock
      Hello,
      Here is my scenario. I have two tables. One with dog houses, each record has information about the doghouse, etc. Second I have a table with each individual dog, each dog is linked to a doghouse by a housing ID. Each dog also has a status of "Active" or "Otherwise." What I want is each doghouse to have a count of how many "Active" dogs are linked to it. I can then do searches and calculations based on that.
      Is there a way to have a doghouse record value that is a calculation, or will I need to write up a script that is run every time I want a report. 
      As a side note I have some 6,000 doghouses and close to 60,000 dogs registered. The faster it works the better.
       
      Thanks in advance!
  • Who Viewed the Topic

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.