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
-
My question is whether to use Table A or Table B
-
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 theDomain-Level-BuildingBlocks
Mapping Table – rather I can directly look up the generated string Id in theLearning 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
Best Answer
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".