geminateCoder geminateCoder - 6 months ago 8
MySQL Question

Connecting a one to many with three tables?

Hello I am learning flask by working on a website and I came across an issue that I can't seem to figure out.

I want to try and make a one to many table which connects through primary keys. The idea is that a user can create a character and if the type is original I can bring up the Original table and display the character based on their id.

My question is: Is this possible and if so should I go about doing it this way or connecting them based on who created the character instead of their id?

This is a diagram of what I thought of trying to do:

Diagram

Answer

Is this possible and if so should I go about doing it this way or connecting them based on who created the character instead of their id?

It is possible, and you can also connect them based on the creator.

Depending on what the data is like and what you are trying to achieve, there are a number of possibilities. I list 3 below

Possibility 1

You can create a view and that has all three joins. If you have a character record that is referenced in the Fandom table alone, then the view will have the columns from the Original and Dungeon tables be null.

That will look a bit like this, for a single record

CharacterView
- Character.id - is not null 
- Character.creator - is not null
- Character.field1 - is not null
- Character.fieldN - is not null
- Original.id - is null
- Original.field1 - is null
- Original.fieldN - is null
- Fandom.id - is not null
- Fandom.field1 - is not null
- Fandom.fieldN - is not null
- Dungeon.id - is null
- Dungeon.field1 - is null
- Dungeon.fieldN - is null

The problem here is going to be that it will be difficult in the database to enforce a constraint that makes sure only one of the three tables reference any single character record. You will have to enforce this constraint in the application.

Possibility 2

You could also set up your tables differently. If all three tables that reference the character table have all the same fields, then you could just combine them into one table and have a type field where the possible values are (Original, Fandom, Dungeon).

Combined Table
- id 
- commonField1
- commonFieldN
- Type (possible choices: Original, Fandom, Dungeon)

Possibility 3

If those tables don't have the same exact fields, then you could use multiple records to make up one entity. For example

TableA
- id 
- field1
- field2
- field3

TableB
- id 
- field4
- field5
- field6

You can replace table A and B above, which are analgous to your Original, Fandom, and Dungeon tables, with one table. Shown below

TableC
- id
- entity_id (shared among all the records that make up one entity)
- char_id
- field_name (possible values field1, field2, ..., field6)
- value

In any case, the creator details like name, etc should be in their own table with a reference in all the other tables. That data should not be repeated in all the tables like it is currently.