Should I use composite key or primary key from other table

Question

We have a Education Project which has following entities :

• Domain (e.g. Programming, UI/UX, AI, ML), each Domain has 5 Levels (1, 2, 3, 4, 5)

• Building Blocks - which are like small topics e.g. java, multi-threading, loops, prototyping, user interviews. Each Level in the Domain is build of multiple Building Blocks.

• Learning Asset (is like a link to study a concept - it can be associated with multiple Building Blocks) and further these Learning Assets are mapped to a particular Domain -> Level -> Building Block

These are the tables that we have thought of :

Domain

|    id     |      Name          |     
|    10     |      UIUX          |    
|    11     |      Programming   |    
|    12     |      AI            |    

Building Blocks

|    id    |      Name               |     
|    1     |       loops             |    
|    2     |     multi-threading     |    
|    3     |     user-interview      |   

Then we store mapping of Building Blocks to a Domain - Level

Domain-Level-BuildingBlocks Mapping Table

|  DLB_Id    |       domainId   |   level      |   buildingBlockId 
|    100     |        11        |    1         |    1
|    200     |        11        |    2         |    2
|    300     |        10        |    1         |    3

in this table - (domainId, level, buildingBlockId) form a composite key

Learning Asset Table

|    id    |       Name                 |   link     
|    1     |      Loop Notes            |    https://a.com       
|    2     |       Operators            |    https://b.com       
|    3     |       Process and Threads  |    https://c.com 

A Learning Asset can be connected to multiple building blocks

Learning Asset -BuildingBlocks Mapping Table

|    id    |       learningAssetId  |     buildingBlockId 
|    1     |        1               |        1
|    2     |        2               |        1
|    3     |        3               |        2

Now the Admin can select if a Learning Asset is applicable in Domain-Level-BuildingBlock combination so

Learning Asset-Domain-Level-BuildingBlocks Mapping Table (Table A)

|    id    |       learningAssetId  |   domainId   |   level      |   buildingBlockId 
|    1     |        1               |      11      |    1         |      1
|    2     |        2               |      11      |    1         |      1

in this table - (domainId, level, buildingBlockId) form a composite key

My question was in the above table : should I again store (domainId, level, buildingBlockId) or should I use their primary key DLB_id from the Domain-Level-BuildingBlocks table

something like this :

Learning Asset-Domain-Level-BuildingBlocks Mapping Table (Table B)

|    id    |       learningAssetId  |   DLB_Id   
|    1     |        1               |      100   
|    2     |        3               |      200     

1. My question is whether to use Table A or Table B

2. If Table B is the correct way should I generate DLB_Id as string by combining domainId + "-" + level + "-" + buildingBlockId instead of using Auto-increment Integet Primary Keys. Will the indexing on the generated string be as efficient as the Auto-increment Integer Primary Key. The reason for generating the string is that when we need to fetch the Learning Assets that belong to a Domain-Level-BuildingBlock combination I don't need to use the Domain-Level-BuildingBlocks Mapping Table - rather I can directly look up the generated string Id in the Learning Asset-Domain-Level-BuildingBlocks Mapping Table

Learning Asset-Domain-Level-BuildingBlocks Mapping Table (Table C)

|    id    |       learningAssetId  |         DLB_Id   (as generated string)
|    1     |        1               |        '11-1-1'
|    2     |        3               |        '11-2-2'

i.e. Table B or Table C ?

Thank you

Answer 1

In general, a mapping table does not need its own id. Instead, the PRIMARY KEY should be a composite key with the 2 (or more) column-ids referencing the tables that are being related in a many-to-many relationship.

For a 1-to-many relationship, a mapping table is unnecessary.

Your "DLB" smells like a "Relation", hence the above comments apply. If, instead, you can reasonably think of it as an "Entity", then it would have its own id. And it would probably have more columns of attributes of the "entity".