Results 1 to 14 of 14
  1. #1
    Join Date
    May 2010
    Posts
    10

    I have a modeling question

    My company just had a new data model created by an outside consultant. Here is my problem I know it is not correct. There are several parameter tables attached to some of the main tables. This is a way to circumvent a super type/sub type design and a way to implement new code without adding a table or at least adding a column to an existing table. Here is an example of my delemia.

    Lets say we have a People table. Then they have designed a PeopleParameter table. Let's say we have Teachers,Students and Administrative Staff and the information needed varies based on what they are.

    I can't even draw the model I have to show you the data.
    People
    People_id Last_Name First_Name
    1 Doe John
    2 Smith Jane
    3 Jones Sue

    Parameter
    Parameter_id Parameter
    1 Teacher of
    2 Student of
    3 Classes
    4 Admin Staff

    PeopleParameter
    PeopleParamter_id People_id Parameter_id Parameter_value
    1 1 1 Art
    2 1 3 Art History
    3 1 3 Sculpture
    4 2 2 Art
    5 2 3 Art History
    6 3 4 Level 10

    Based on this Model (most of the logic is in the code)
    John Doe is an Art teacher he teaches Art History and Sculpture.
    Jane Smith is a Student her major is art and she is taking Art History.
    Sue Jones is admin staff pay grade level 10.

    I know this model is not correct but what rule did it violate(other than common sense) Can anyone point me to documentation or any books on data modeling? I know how to correct the model so sending me the corrected model is not my answer. I need to be able to document the flaws in this design. I am the dba so this model will be thrown over the wall at me, once it starts to have problems down the road it will be my problem. After all the data model was designed by experts.

    Thanks
    PJ

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It sounds like the Associative Model of Data.
    Associative model of data - Wikipedia, the free encyclopedia
    It's been discussed on here a few times but TBH it is only of academic interest to me and most of the guys here - I don't think many if any of us have stumbled across it in the wild.
    Discussions of AMoD in DBF
    My condolences
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, since I haven't dealt with this model, my starting point would be to consider all the common queries that would be required by the organisation and write the SQL required by this model and your preferred 5NF\ 3NF design.

    If you are presenting the efficacy of two competing designs to the stakeholders then better to avoid dry academia (especially since you are coming from a position of ignorance compared to the consultants) and focus on the nults and bolts they can understand. Usually alternatives to the relational model are seductive when measured by the flexibility and ease data can be inserted, and highly expensive when the practicalities of extracting information (not data, information) are taken into account. It might not be the case here, but it is a good start since the cost of writing 5 lines of code Vs 25 lines of code can be understood by anyone.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Depends on what "rules" you're going by. Generally if you're using a relational database, you're going to start with rules of normalization. This violates nearly all of them at every level of normalization, take your pick.

    As far as selling the need to rework it, focus on the benefits of a relational approach that you are NOT getting by allowing this to remain in production. The academic support I've seen for the associative model is just that, academic. Ignoring the actual industry implementations of the "relational model" and sticking to the books, one can have a compelling conversation about the mathematical purity of each approach. Back in the real world, one better have a really good reason for why they ditched baked-in features like constraints, indexes, atomicity/consistency, etc.

    For you it sounds like maintainability is tantamount. Instead of blowing holes in the associative approach, I'd instead focus on increased OVERALL efficiency, stability and security you'll gain from moving to a less bizarre approach.


    Quote Originally Posted by pootle flump View Post
    If you are presenting the efficacy of two competing designs to the stakeholders then better to avoid dry academia (especially since you are coming from a position of ignorance compared to the consultants) and focus on the nults and bolts they can understand. Usually alternatives to the relational model are seductive when measured by the flexibility and ease data can be inserted, and highly expensive when the practicalities of extracting information (not data, information) are taken into account. It might not be the case here, but it is a good start since the cost of writing 5 lines of code Vs 25 lines of code can be understood by anyone.
    This. The academic merits of either approach are completely worthless in production IMO. Academically, I can argue that a non-relational model gives me EXTREME flexibility and blinding speed as an application developer. In the real world, this is a bad, bad, BAD idea. Discipline and rigor are difficult and expensive enough to implement in your average SDLC with a "restrictive" relational model. Just how easy do you want to make it for developers (and possibly users) to circumvent business rules with lightening speed at the expense of everything else? Then there's maintainability... I'm guessing you don't need any help poking holes in that one.

    But really, stack up the merits, not the drawbacks. Bump those up against your business requirements, which one makes more sense? There aren't many scenarios (though they DO exist) where a wholesale departure from a relational approach is well warranted. There's a point where performance and flexibility outweigh the possibility of inconsistent or incorrect data (think Google/Facebook). Chances are you'll know if you're in one of those scenarios long before you actually start building anything.
    Last edited by Teddy; 10-08-10 at 18:22.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think Teddy and I are agreed - fight the war in the trenches not in the simulator.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by pootle flump View Post
    fight the war in the trenches not in the simulator.
    Ohhhhhh.... I'll be stealing that one in the near future.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    May 2010
    Posts
    10

    Thanks

    The more I look at the model the more I go nuts. I think my best course of action is to smile and nod and find a new job.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - according to the forum software it took you 10+ mins to write that. I suspect you have been looking hard at the model.

    Out of curiosity, do you working in the education sector? Private or public? US or elsewhere?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy View Post
    Ohhhhhh.... I'll be stealing that one in the near future.
    *pleased*. The secret it seems is to imbibe a couple (or more) mind sharpeners. I'll be shipping the ingredients for an Old Fashioned to my office on Monday.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2010
    Posts
    10

    Wow

    There must not be any test for a data modeler. I just found another gem. We have a locations table. Fixed point on earth based on lat and long. Every table that needs an associated location has a relationship to this table. That is not the problem. Locations are not re-used. The locations table has a locations_type column that will tell the application where it points back to. So the same lat and long will be stored multiple times in this table because if it is a business location then it has a locations_type of B, if it is a hospital then the locations_type is H. If a Hospital and a Business are located in the same place there are 2 entries in the table.

    I need to smile and nod, drink heavly and look for another job.


  11. #11
    Join Date
    May 2010
    Posts
    10
    Quote Originally Posted by pootle flump View Post
    Lol - according to the forum software it took you 10+ mins to write that. I suspect you have been looking hard at the model.

    Out of curiosity, do you working in the education sector? Private or public? US or elsewhere?
    The US Goverment. Didn't give real examples but it is the exact same concept.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. I was going to suggest fight your corner if you are in an educational establishment. If central government, and they have anything like the central government ****fest over consultants we have here in the UK, then your "run" plan sounds good.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Ah, that makes it trickier. I've found it to be considerably more difficult to argue a real world cost/benefit analysis on the gov't contracts I've fielded. It's a bit tricky to convince folks to care about efficiency and reliability as a function of cost when they don't have the intrinsic motive to remain profitable, like most private sector gigs.

    I'd like to throw my condolences in the bucket with pootle.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    May 2010
    Posts
    10
    Quote Originally Posted by Teddy View Post
    Ah, that makes it trickier. I've found it to be considerably more difficult to argue a real world cost/benefit analysis on the gov't contracts I've fielded. It's a bit tricky to convince folks to care about efficiency and reliability as a function of cost when they don't have the intrinsic motive to remain profitable, like most private sector gigs.

    I'd like to throw my condolences in the bucket with pootle.
    I think they knew they needed a re-designed database. They think what they are getting is better then what we have now. With Mgt it is like arguing that wall is white. The new model will get implemented because politically it has to.

Posting Permissions

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