I am planning a database scheme and I hesitate between two designs, which one should I choose?
For the purpose of the question, assume we want to prepare a database scheme for an application managing student information in a university. There is several modules in the application:
Registry, dealing with general informations, like date of birth, health insurance number, and the like.
Library, dealing with the properties useful to the library management, like the number of books withdrawn, quitus or penalties.
Exams, dealing with the exams taken and the grades.
The modules Library and Exams see the modules Registry, but apart from that, are independant.
In the application scenario, there is a very large number of students, data is written once, seldom updated and often read. Furthermore, the university expands its system each year, so that modules are added: Sport, Campus, whatever. Modules remain quite independant.
I hesitate between two database layouts.
In the first layout, a table
MODULE is associated to each Module and
UID is used as primary key. Since each module needs the
properties under control of Registry, we also prepare views of the
UID, so that the database knows
we will use this join extensively.
When the system expands, we add a new table and a new view to reflect this expansion.
In the second layout, we create a table with a large number of columns holding the properties of the various modules.
When the system expands, we add columns to the tables.
How does these two approaches compare?
If, say, some software upgrade comes with a major bug, requiring to downgrade the software and perform later a second upgrade, the first layout seems to be more robust. Regarding perfomance, the second layout saves a lot of join operations, but in the first layout, we defined views for the join operations, thus advertising which complex operations are likely to happen so that the database system can plan this. I am far from a database expert, but am I right if I think that if I put all relevant information into the hands of the database system, it will be able to properly carry operation?
If I did not see it used in industrial applications, I would never had given 2 pences on the second layout. But since I did, I would like to get other advises.