Entity Relationship Diagram: What is a composite ID .Net Core 6

I have 3 tables in the database and I wanted to know what a composite primary key is.

E.g: Table 1 is called superheroes Table 2 is called categories of superheroes and table 3 is called biography of superheroes

I want to create an ERD (Entity Relations Diagram)

The attribute key is supposed to retrieve the json file and display the superhero category as needed, is it possible or am I completely wrong?

This for a .net core project to learn

Answer

Composite keys are seldom used with technical IDs. They are more common with natural keys.

There are superhero categories. You’ll have a table for these categories and probably give each category a number, so you can uniquely identify a category with this. You could just as well use the category name as a unique identifier, but then you’ll have problems if you want to use a different name some time, e.g. rename ‘Ability to fly’ with ‘Flying ability’.

Then there are superheros. You’ll have a table for them and each superhero will either get an ID or simply be identified by their unique name. One of these two columns will be the primary key. Let’s say you use the superheros’ names.

As there is no separate table mapping superhero categories to superheros, you’ll have the category ID in the superhero table, so each superhero belongs to exactly one category.

If you want a superhero to belong to multiple categories, you’ll need a bridge / association table for this. In your ERM you may only show the superhero and the category table and use an m:n relation arrow or show all three tables with 1:n relations. This is just a matter of the granularity you want to show in your diagram.

Now let’s say your biography table can contain multiple biographies per superhero. This means there must be the superhero name in the biography table to establish this relation. The unique key for a row in this table would probably be the combination of superhero and biography author. Two columns combined identify a row. A composite key. And if you make this the primary key, you’ll have a composite primary key.