Which database schema should I pick?

Question

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.

First layout

In the first layout, a table MODULE is associated to each Module and a UID is used as primary key. Since each module needs the properties under control of Registry, we also prepare views of the join of REGISTRY and MODULE on 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.

Second layout

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.

Comparison

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.

Answer 1

If I did not see it used in industrial applications

Seeing others do something in a certain way is not a motivation to do so yourself. Unless you know why those applications were modeled in that way, assume the worst: they did not know what they were doing and the model evolved into this monstrosity over time.

Denormalization has its place, but when used, it usually are very specific optimizations that should only be chosen after careful consideration.

So: do as you should, normalize, go with the first layout.

Answer 2

1st Layout

The absolute worst case I ever saw for something similar to the second layout you describe was as follows:

We had separate stored procedures for doing searches based on manufacturer, model and part. Each of these stored procedures were fine tuned to specific indexes.

Because it was deemed to difficult to maintain, someone decided to create one gigantic search stored procedure that branched to different internal sections based on criteria. It was a massive failure.

The query plan stored in memory for this "new gigantic search" stored procedure never got optimized because of the branching that took place inside the stored procedure itself. So in essence, this new stored procedure wound up doing full table scans each time it was called and it was called a lot. The system would often crash or timeout.

Morale of the story... don't take shortcuts.

Answer 3

(At the risk of shouting) First Layout - DEFINITELY.

the second is an Entity-Attribute-Value model which, in all but a handful of cases, causes far more problems than they solve. Also, you state that "Modules are independent"; mash them altogether into an E.A.V. model and they're forever intertwined.

I would suggest:

Table REGISTRY

• Primary Key on USER_ID
• Secondary indexes on names and other, commonly-searched fields (watch out for the case of these values; many DBMSs regard "A" != "a").

Table MODULE

• Primary Key on MODULE_ID.
• A module must be able to exist without any students, so USER_ID cannot appear in this table (except, possibly, as a foreign key to the Module's "owner").

For Students registered on a module, you'll need a "linking" table:

Table MODULE_STUDENT

• Primary Key on USER_ID and MODULE_ID.
• Secondary index on MODULE_ID (and USER_ID).

If you're using referential integrity (and you should), then add foreign keys to validate the values held in this table:

• MODULE_ID -> MODULE.MODULE_ID
• USER_ID -> REGISTRY.USER_ID

Where necessary, join across REGISTRY, MODULE_STUDENT and/or MODULE to get all the related information.

Views will not help the performance of your system.
Correct indexing, of the tables underlying those views, will.

(And Views will [probably] make your application coding easier).