I am learning sql now and practicing the scenarios to design the tables. I have a one scenario where I could not find proper suitable table structure.

The scenarios is as follows, I want to store depedencies user journey in sql. For example, in customer creation journey, we need to create valid sector, language and country codes in the system. Another example, to create a new account (bank account), we need to create the sector, language and country followed by customer.

So far, I could think of following table design, but I am sure this is not good as there is no primary key and not following the normalization standards.

I understand that this is many to many relationship as one journey can have many dependent and one dependent can be associated with multiple journeys. I need help to efficiently design the tables in sql, please can anyone help on this.

CodePudding user response：

You need from the intermediate/join table that should look like this -

Table name - journey_dependent 
Coll(Jurney_FK)  Coll(Dependent_FK)
journey_id       dependent_id

You can check more here - https://www.baeldung.com/jpa-many-to-many#1-modeling-a-many-to-many-relationship

CodePudding user response：

If journey and dependent values are PK in origin tables, you have 2 FK. You can create a composite PK on that table with that 2 columns.

Maybe order need to be in dependent table. If not, there is information on that table : order. So this is not a pur relationship table. So you could optionally had a technical PK column (auto increment) on it.