Should I use composite key or primary key from other table

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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".

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply