CODEWITHSUNDEEP
mysqlsqldjango
techpanda
techpanda

Reputation: 11

How to model my database for a one-to-many relationship

I am learning backend development in Django and came across this problem.

Say I am designing a travel app: I have two databases USER and TRIP with one-to-many relationship. i.e., each user can have multiple trips and each trip can be taken by only one user.

TRIP has a column trip-number. I would like to auto-increment this column for every row added into the TRIP database BUT the increment should be independent for each user starting from 1

TRIP table

user trip-number
1 1
2 1
1 2
1 3
2 2
3 1

Something like that? I cannot auto-increment the whole column as it has to be unique for each trip taken by the user BUT the numbers can be same across different users.

Ideally I prefer to do this automatically on the server-end (sql) instead of writing a logic in the client. Any help would be appreciated. Thank you

Upvotes: 0

Views: 80

Answers (1)

Popeye
Popeye

Reputation: 35920

Using trigger to update the trip_num column is one way to achieve the desired result but I would suggest not to use the triggers as it has many disadvantages. Few of them are as follows:

  • you will need trigger for insert/delete/update actions.
  • In insert trigger, you will have to assign trip_number per user.
  • In update trigger, you will have to check if user column is updated, if yes then you will have to arrange trip_number accordingly.
  • In delete trigger, you will have to arrange all the trip_num, if one of the record except last record of the user is deleted.
  • If two transactions occur at the same time for same the user then there will chances that triggers assign the same trip_num to different rows of the same user.

So, to avoid it, you can use the view as follows:

Create view vw_name as
Select t.*, -- use column names and remove trip_num column 
       Row_number() over (partition by user order by trip_date) as trip_num
  From your_table t

Upvotes: 1

Related Questions