No results

    Suggest denormalization of a SQL schema

    Ease migration from SQL with denormalization suggestions.  Hackolade Studio helps with the migration from SQL. You can now reverse-engineer a Data Definition Language (DDL) file from Oracle into a Hackolade model. Then you can apply denormalization functions for embedding and referencing data, and truly leverage the power and benefits of nested objects in JSON and MongoDB.


    You should save the resulting model as a base, for example v0.  Then it is suggested you save it again as v1 and work with the denormalization function to make v1 evolve towards a denormalized model.




    The user needs to have a fairly good idea of where he wants to go with the schema design, and use Hackolade to facilitate the work.  For example, with the Northwind database shown above, in a NoSQL model, one would want to merge the tables 'orders' and 'order details' while embedding some data from the master tables around.


    Denormalization dialog


    Select 2 tables to which denormalization should be applied.  Then select the type of embedding (sub-document in child, array in parent, or both.)


    Example of embedding of sub-documents in child.

    Collection 'products' contains a foreign key to 'categories':

    Denormalization -- sub-document in child - 1


    the resulting denormalization would be:

    Denormalization -- sub-document in child - 2

    with the field category_id in 'products' being replaced by a sub-document with all the fields of the 'categories' collection, including one foreign key and several foreign master relationships.  Depending on your needs, you may want to keep the 'categories' collection, or delete it.


    Example of embedding an array in parent.

    Collection 'order details' contains a foreign key to 'orders':

    Denormalisation - Array in parent - 1


    the resulting denormalization would be:

    Denormalization - Array in child - 2

    where you notice that all fields of 'order details' have been added in an array of sub-documents in 'orders'.  And it makes sense to delete the collection 'order details'.