Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Plz help me in Relational data modelling

    Hi all

    I have been working in Data warehouse environment on etl and data modelling for data warehouse(OLAP)

    Now I got a data modeler job on the OLTP environemnt. I am totally clueless on how to start data modeling here .

    For dwh i used to first look at reporting requirements then back track to a data model.. what will be the starting point here in OLTP.

    on top of it...

    The application is totally developed in JAVA,J2EE,webservices,Message Queue etc .The documentation of the project obviously talkes about j2ee developement ..

    what should be my approach to start? i know we have to start identifying entities then attributes and then relationships etc

    but i am not even understanding the documentation..where it talks about the spring,hibernate ,jsp etc

    please help me ... am very much worried about the job now i want to learn the approach to OLTP data modeling.

    There wont be any reports as the starting point the way we had for DWH. so please suggest ...

    thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's the oltp database about? onion farming in texas? water rights in the strait of juan de fuca? registry of kitten neutering in albania?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    6

    Hi

    hi sorry for not being clear..



    i have to model a alert service management.......

    SCenario:

    1) Users register through a User Interface and subscribe for an alert.. there are subscription types
    2) Users can register only to those alerts to which they are entitled for.. i.e there is something called as Entitlements
    3) Alerts are provided by various systems example Payments, reports system etc

    Ex: if your account balance came to minimum then payment system will send you an alert

    4)Alerts are sent through..various channels like :sms,email etc

    5)Alerts have alert types ex: payment alerts, report alerts.

    I have to model the system for the above scenario...

    Question: should there be individual tables for payments and reports etcc or all the information should be in a single table

    and what are the tables that we can design here ...
    Last edited by rajesh_tns; 03-14-09 at 12:10. Reason: need to add more information

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajesh_tns
    what are the tables that we can design here ...
    well, based on what you said, these would be good candidates --

    - alerts table
    - users table
    - entitlements table
    - user_alert_subscriptions table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    6

    hi

    thank you very much r937... few more questions
    1)
    do we need to create separate table for alerts from each system

    Eg: payment alert
    report alert

    payment entitlement
    report entitlement

    or include all alerts in a single table...
    2)
    Another questions is ... do we need to go for alert type table ?

    if we separata table then again we have payment alert type,report alert type etc..

    3) do we also go for alert source table ( eg: source is payment,report etc)

    4)How do i know that we have covered all entities with out missing any entitiy..


    on what basis do we decide this ?whether to create separate tables or a single table

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use one alerts table, with columns like type and source to indicate how they differ
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    6

    Hi

    thanks a lot r937 for your help...

    regards
    raj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •