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.
Quote:
| 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
.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
