-1

I am creating new database - for that example let's say I want to create a table with characters from a game, the next table with their teams where primary key of this table is foreign key in table with characters and the last table with expansion packs released for that game where primary key of this table is foreign key in the teams table (as some teams were added to game with an expansion pack). Unfortunately some characters belong to unknown team so probably it should be shown as NULL, but at the same time we know about expansion pack that added those characters to game. Please see below image to make my description more clear:

example image of database tables

I would like to get info about an expansion pack for the character who belongs to an unknown team (character3 in the image). Is the only one way to do that by adding a second foreign key with expansionpack_id to the character table? Is it a correct solution?

Cleptus
  • 3,446
  • 4
  • 28
  • 34
Damien
  • 1
  • 1
  • 1
    Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Mar 04 '20 at 17:28
  • 1
    Time to follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Dozens of published academic information modeling & DB design textbooks are online free in pdf. stanford.edu has a free online course. (But asking for resources outside SO is off-topic.) – philipxy Mar 04 '20 at 17:30
  • Possible duplicate. See this answer: https://stackoverflow.com/a/11824531/3854195 – Morpheus Mar 04 '20 at 17:43
  • 1
    Please show the exact new design you mean & try to query for what you want & ask a question if you get stuck. Please don't just wonder & ask us to work it through for you. [mre] – philipxy Mar 05 '20 at 01:34

1 Answers1

0

Tables Character and Expansion Pack are related through Team table. Table Team is working as a Mapping table in a Many-Many relation.

Hence character3 in image doesn't have a relation to Team table so you can not relate it to Expansion Pack.

Suggestion: Move expansionpack_id column from Team to Character table.

donut
  • 790
  • 5
  • 11