1

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?

1

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.

0

The way you've outlined it is how I would do this in a data warehouse. I've created relationship tables (mapping tables) to tie different dimensions together, because there are potentially many to many relationships between them. And because my tables have thousands and millions of records each, creating a denormalized table would be incredibly cumbersome and inefficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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