Normal beyond convenience

Had a discussion with Joel and Eric on a table structure today.

 

The issue:

 

There is a table like this:

 

“course” table

 

Course_id	Degree	Fee	Type
1	COMMERCE	1000	UG
2	COMPUTERS	2000	UG
3	LAW	1500	UG
4	COMPUTERS	2000	PG

 

The issues this was:

1. The “course” table represented both the entity (degree) and the relationship (course to degree).
2. Look up course_id 2 and 4. They both represent COMPUTERS degree and are repeated here. The Degree for course_id 4 could have very well been “computers” instead “COMPUTERS” although both refer to the same degree.

 

So I wanted to split this table as follows:

 

“course” table

 

Course_id	Degree_id	Fee
1	101	1000
2	102	2000
3	103	1500

 

“degree_lookup” table

 

Degree_id	Name
101	COMMERCE
102	COMPUTERS
103	LAE

 

Note: In my business case, it was highly likely that only 2 or more courses would be added in the next 10 to 15 years.

 

But Eric indicated that the above solution would be good if there were a lot of courses already or a lot of courses were going to be added. But this was not the case for us. So the above solution would be normalization beyond convenience (writing all the domain API objects, SQL, hibernate mapping etc…). So to take care of disadvantage 2 above, there could be a check constraint inserted on the table. That way only COMPUTERS will be accepted. In this case it would make sence. But if there were 20 or 30 courses, then it would not make sense to put in 20 or 30 elements in the check constraint and in that situation, my solution would work very well.