I have a question how I should design this relational database. I have a main profile, which has 2 sub-profiles, which are weak entities. These sub-profiles represent counterparts of a business process, an Employee and an Employer.
Given I want to have tables that are related to these sub-profiles, such as “Review” or “Job Posting” (Similar to LinkedIn), which approach would be the most suitable:
- Creating a separate table for each profile type, e.g.,
EmployerReview and EmployeeReview - Creating a single unified table, which has fields to determine which sub-profile type is sending and receiving the data. e.g.,
Review
- sender models.ForeignKey(Profile…)
- receiver models.ForeignKey(Profile…)
- sender_type models.CharField(max_length=15, choices=PROFILE_TYPES)
- receiver_type models.CharField(max_length=15, choices=PROFILE_TYPES)
My main concern is the performance issues, e.g., when wanting to query all EmployerProfiles and the related Reviews of that profile.
Now, If I have understood correctly, in method 2. you would have to do filter query, which is obviously slower than select_related (Similar to SQL Join?).
The gain would be more flexibility and simple complexity, but there would be performance loss.
Which of these methods would be more standardized or optimized way for such a problem?
I tried creating both of the solutions, and they both work, but I am not sure if performance will become an issue with method 2. if the backend userbase scales a lot.