I'm new to database designing for real-world applications. I have an idea for an application where each student could leave an opinion about a university at which one was on an exchange (I will use Django + PostgreSQL if that helps). While I was thinking about the database design I came up with this schema:
The reason for such a design was that there will be a lot of information to store. The user, however, should be able to look for an opinion giving just his e.g. destination university. He should also be able to narrow down the scope of the search by providing additional information.
However, when I think of the schema right now I'm unsure whether I should add separate FKs to the Opinion table to avoid such FKs chain like in the image, or is it just fine?
Is a chain of foreign keys better than duplicating data
database-design
Related Question
- Configuration Table
- Mysql – How to prove that the act of removing foreign keys doesn’t corrupt existing data
- Database Design question
- Mysql – Database design: sharing tables between databases
- Table Design for Different Constituents
- How to store array values
- Postgresql – Code review database design for storing college assessments & enrolments
Best Answer
I would keep this style of design. The only issue that this could begin to cause is a performance hit on reports when dealing with a lot of data, but you wouldn't start seeing it until the millions of rows range. This would be because any report would have to basically query a half dozen tables to get the information it needs, but in general this would give you a lot of flexibility to create queries and views to sort and filter the rows.
In the real world, you'll likely find that the University table would contain the fields for both Country and City, with them just as commonly being just text fields, instead of having lookup tables. This would improve query performance a bit, especially since the number of universities would not be nearly as large as the number of faculty or students. The cost for this would be an increase in storage requirements, since every row in the University table would have the full city and country names instead of the FKs.