Normalization of Database, Pros vs. Cons

Normalization of Database, Pros vs. Cons

  

Hi,

I was wondering if it makes sense to normalize your database structure on OutSystems? I normalized one database table and as a result I now have 12 different database tables, which are, of course, linked to each other via foreign keys. 

I notice two cons: 

1. Increased complexity (data handling, esp. when working with an existing sometimes inconsistent database) and therefore increased time to create the app

2. Increased costs: Before: 1 Table online + offline = 2 AO, After: 12 Tables online + offline = 24 AOs + increased labour costs

Pros:

1. A nice database strucure


Weighing up the pros and cons, what do you think would be the way to go here? 

Thanks and best regards

Sebastian

I prefer Normalization, at least to 3NF for maintainability, performance, scalability  reasons.


Solution

Sebastian,

Without knowing what the original table and data looks like it's difficult to help you create a better layout but you are looking at this incorrectly.

Increased complexity - while I understand a database with 12 tables is more complex than one, your code will become much more complex when you have to deal with all the duplicated data.  For example, if you have a field showing the status (Active, Inactive, etc.) you'll have to write additional code to handle that when it is a value in a table instead of a link to a lookup.  If you add a new value, another code change.  Implemented with a proper lookup table all this can be handled without changing code.

Increased costs - I understand the OS pricing model impacts your decision but I'm sure some reasonable compromise could be found.  Also factor in the reduced maintenance costs in my previous example.

A nice database structure - yes, doing a proper 3NF database is nice but pays huge dividends when you start writing queries and screens.  If you put it all in one table it'll be more work in the long run, performance will suffer and making code changes will be more difficult.

Read this for more information -
https://en.wikipedia.org/wiki/Database_normalization

Hope this helps,
Curt

Solution