none
Data base modeling advise

    Question

  • Dear all,

    I need to define a database modeling for a business case and actually I do not know what is the best approach to go for it as I have found that there exist deferent type of modeling design patterns :

    One common is until the 3rd form of modification, I have heard also about stars schema modeling and Vault modeling design

    What is all about this and when to use ?

    is there a good guide line resource which explain with examples which to use and how to apply it ?

    Any good book also on this ?

    regards

    Friday, June 1, 2018 9:02 AM

All replies

  • Hello,

    3rd normalization form is mainly used in OLTP databases, like for ERP or other line-of-Business Software, while star Schema is used for data warehouse (OLAP) and reporting databases, so the choose of what to use depends on your requirements.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 1, 2018 10:00 AM
    Moderator
  • Thanks,

    Do you have good reference link or even books which describe with sample OLTP and OLAP design modeling guide lines ?

    regards

    Friday, June 1, 2018 11:48 AM
  • Friday, June 1, 2018 12:15 PM
    Moderator
  • There are many different database design patterns and literally thousands of books on database design.  Different patterns are used for different circumstances depending on your goals. 

    If your database a OLTP database and generally many small reads/writes to small numbers of records.  Then you might use 3rd normal/star.

    If your database is mainly reporting, having large queries returning many rows.  You might use star or vault or something else entirely for speed of reporting.

    There are no real "rules".  Every method has pros and cons.  You need to evaluate your usage and which would be best for your current situation.

    Friday, June 1, 2018 12:43 PM
    Answerer
  • What exactly is your business case?

    Based on that you need to use a corresponding data modelling approach

    For example if its OLTP, it would mostly be normalized until 3rd level atleast (3 NF)

    The reason being for OLTP systems the emphasis is on optimizing data storage and avoid any DML anomalies by reducing redundancy

    If your business case is to develop a reporting/analytic system, then you can use star based desiggn where data will be held as dimensions and facts (dimensional modelling) with facts linked to related dimension

    https://dev.to/pedrojmfidalgopt/star-schema-vs-snowflake-schema-and-why-you-should-care-40fh

    vault modelling is different and is more suited for building EDW

    more details are here

    https://en.wikipedia.org/wiki/Data_vault_modeling


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 1, 2018 1:42 PM