Sql-server – SQL Table Design Schema – To Normalize or Not

database-designnormalizationsql server

I am considering a table schema design for a project.
The design involves a Many-to-Many Relationship.

The 2 related tables are:

a) Class

b) Subclass

A Class can have many Subclasses, and a Subclass can have many Classes.

Here is my Normalized design:

enter image description here

Is it best to always do this design? The relationship will have 20 rows max. Is it then better to create a single denormalized table as it will then be much easier to update and delete data?

In the Normalized design I would have to create a View to join the tables to display data.
And also to create sprocs or triggers to update the base tables.

The denormalized design involves updating/deleting/selecting from a single table.

Denormalized design:

enter image description here

Is it always best practice to use normalized design?

Best Answer

This looks like a school project correct? I think the confusion is coming in because your example is very simple (perfectly appropriate for a class/training environment). The design you have is considered best practice. The reason it might seem simpler to go with the denormalized design is because you have so little data/so few columns.

If you add a dozen columns each to the Class and Subclass tables it starts to look worse. Then if you add a million rows it gets even worse. When you design tables you can't always know in advance how big they will get and generally they will be wider than in your example.

Another reason why it's best practice is because you are logically separating your data. What if you need to tie/use subclass without the class table? Or class has an additional purpose. If they are all in a single table you can't break them up like this.

Think of the same structure using School & ClassName. The School table could be used for any number of other purposes (to tie employees to for example). Same with the ClassName (list of required classes for a degree) table and even the join table (students taking the class at a given school). But if you have a single wide table you wouldn't be able to use them that way.

Related Question