#1
  1. No Profile Picture
    Cthulu-ish
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Seattle
    Posts
    31
    Rep Power
    13

    Object/Relational mapping and the array datatype


    I've been developing an application in Java/Postgres and have been butting my head against the old Object/Relational mapping problem. Ideally I'd like the application to have as little SQL in it as possible and all of the persistence methods to be in a manager/factory class.

    Sounds nice but since the object model and the relational model are completely different I have to have a unique SQL call in each persisted object to cover each object's different footprint in the database.

    Now to the question- I've always been a bit puzzled at the use of the array datatype in postgres because that sort of thing seems to violate normalization (not that I have any real sacred cows in that respect but if I'm going to do it I want a good reason). I haven't even tried but is it possible to use the array type to store foreign keys for another table, thereby getting rid of the need to have a ton of extra "relational tables" in the case of many to many relationships? Is this a retarded question? I haven't even gone about testing the idea because I'm already so far into this particular project that any major change like this would have to wait for version 2.x or later.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I don't think you can directly use the array type in the way you describe, but possibly with the help of triggers/rules and some careful thinking, you could create some constraints and logic to make your array column behave this way. Whether you should do it is something I can't answer without knowing more about your app. Array types don't necessarily violate normalization, but using them to institute some sort of "pointer" scheme might. (Am I right in guessing you want the equivalent of pointers?). Remember, with the abstraction capabilities of a DBMS like PostgreSQL, having many base tables is not necessarily a bad thing, since you can do some incredible things with views and functions.

    Sounds nice but since the object model and the relational model are completely different I have to have a unique SQL call in each persisted object to cover each object's different footprint in the database.
    I have found this kind of discussion to be one of the more interesting areas of relational database design. I think many developers have sort of a mental block about the whole problem of relational DB vs object-oriented development. (It shouldn't be thought of as a "vs" thing, but that's part of the mental block)

    1. Relational databases shouldn't just be thought of just in terms of access to individual tables via SQL queries. This is especially true when your DBMS has access to sophisticated levels of abstraction such as views, functions, triggers (and rules, such as in PostgreSQL), constraints, domains, and user-defined types and operators. In fact, all of these features actually allow you to use encapsulation, and even a sort of inheritance.

    2. Normalization is only one of the facets of relational database design, and I feel it is still poorly understood by many. Yes, the relational model strongly recommends decomposition along orthogonal lines to your application classes or modules. But, there is nothing in the relational model that prohibits complex types such as arrays and more, as long as the methods by which you manipulate those types are relational, and don't involve such programmer shortcuts as pointers, 'object IDs", and other things which violate Codd's "Information Principle". In fact, theoretically a datatype can be as complex or simple as you like, as long as you have a well-thought-out reason for using it.

    3. One of the "fathers of relational writing", C.J. Date has a lot to say about object-oriented thinking and how it can interact with the relational model. For example, he disagrees with the common approach that classes->attributes should always be mapped to tables->columns. See www.thethirdmanifesto.com and consider buying his book "Foundation for Future Database Systems: The Third Manifesto". Now, I have to stress that the database systems he hopes for do not fully exist yet, and do not use SQL, but still the thinking can be an eye-opener, and PostgreSQL comes closer to fitting these ideals than most SQL systems out there. I'm not saying that mapping classes to tables->attributes is necessarily wrong, but its not always the only way, and perhaps many design decisions could be re-thought.

    4. Even if you decide that your classes should map to tables in the "standard" way, you might want to think about using PostgreSQL's set-returning functions, as mentioned in this post. In fact, set-returning functions can be combined with custom "Composite types", so that they respond to queries, behaving just like views, and returning rows of data, but internally can process all sorts of logic, and involve as many tables as you want. Think of them as abstract table types, if you want .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Cthulu-ish
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Seattle
    Posts
    31
    Rep Power
    13
    I'm a bit of a lurker here and I've always found reading your posts informative, this one is no different

    O/R mapping seems to be one of the biggest stumbling blocks in database aware applications. I have a tendancy to over-normalize (nearly 5NF) and then incrementally denormalize until I hit a happy medium. In the case of this project with postgres I've made some pretty heavy use of views and stored procs. I imagine that the best way to treat this particular issue is to be a bit more creative on the application end. Something such as having the main factory/manager class loop through the attributes of the persistable object and make the function call accordingly.

    such as
    Code:
    StringBuffer sb = new StringBuffer("sp_insert" + className + "(");
    for(int i=0;i<setterMethods.length;i++){
       if((i + 1) < setterMethods.length){
          sb.append("'"+value[i] + "',");
       }else{
           sb.append("'" + value + "')");
       }
    }
    roughly anyway. I think you're probably correct in saying that it's the wrong approach to use arrays like I was considering doing and I'll probably just go ahead and do it like this.

    thanks for letting me bounce that off of you though

IMN logo majestic logo threadwatch logo seochat tools logo