Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Your second layout sounds like an Entity-Attribute-Value or object-attribute-model. Search for pros and cons of this approach. –  JeffO Nov 22 '13 at 17:05
    
JOins are good, you want joins. You just need to index teh joins fields properly, databbases want and expect joins. –  HLGEM Nov 22 '13 at 18:30
    
@JeffO how is the second layout an EAV schema? I don't see it. –  RibaldEddie Jan 31 at 19:48
    
I missed the "add column" part and thought you were going to have one column for properties. –  JeffO Feb 17 at 20:20

3 Answers 3

up vote 9 down vote accepted

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.

share|improve this answer
1  
Seeing others do something in a certain way is not a motivation to do so yourself. You are definitely right, to me it was just a motivation to ask Why are they doing this? –  user40989 Nov 25 '13 at 8:29
    
While I appreciate experience return given by @CapeCodeGunny, your answer provides useful references to appropriate literature. I think your answer is the best! –  user40989 Nov 29 '13 at 12:23
    
I wouldn't call Wikipedia "appropriate literature", but am glad to have been of help! –  CodeCaster Nov 29 '13 at 16:16

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.

share|improve this answer
    
One of many reasons to avoid stored procedures. –  Mr Cochese Nov 26 '13 at 23:41
    
The issue is not stored procedures. Stored procedures are awesome. The issue is lack of database knowledge. –  Cape Cod Gunny Jan 31 at 21:18

(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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.