0

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

2
  • Two questions 1. Can a BuildingBlock occur at more than one level? 2. Are learning assets always URLs or are there other items you are linking to? General comments: 1. You should always use composite primary keys unless the key itself becomes too wide (in bytes, not columns) 2. When starting a data modeling exercise, don't add surrogates until the very end - data mock-ups are fine, but replacing things with numbers obfuscates things unnecessarily.
    – user212533
    Jul 26, 2021 at 14:23
  • 1. Yes BuildingBlock can occur in Different Domains at different levels. 2. Learning Assets will be URL only What are surrogates ?
    – j10
    Jul 27, 2021 at 8:06

1 Answer 1

2

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

4
  • Thank you Rick. So if the mapping table has some additional fields like deleted_At, deleted_By it is still ok to just use the composite keys right?
    – j10
    Jul 27, 2021 at 8:07
  • 1
    Yes, it is OK to have other columns like that. See also mysql.rjweb.org/doc.php/… . The composite PK is for performance; deleted would not be included in the PK.
    – Rick James
    Jul 27, 2021 at 19:57
  • In DLB table we have a composite key on (domainId, level, buildingBlockId) but most of our look up is going to be to find building blocks at a different levels of a domain - so shall we define index on (domainId, level) as well
    – j10
    Jul 29, 2021 at 10:03
  • 1
    @j10 - No. If you have INDEX(a,b,c), you do not also need INDEX(a,b). That is, if an index matches exactly the left part of another index, it is unnecessary.
    – Rick James
    Nov 27, 2022 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.