Upgraded from 1.6. I’ve already done some playing on 2.0 on htmldb.oracle.com, already liked the little interface and management improvements.
I’m not too interested in all the bells and whistles – I just want to see first if I can produce usable maintenance forms for my preferred flavour of tables. I’ve been using the wizards to create all my pages, but have come across problems whenever the tables use natural PKs (specifically, when I try to make master-detail pages) – so far most problems have been resolved by adding a PK populated by a sequence from a trigger. Not what I really want to do; I’m hoping that I can find a way to get HTMLDB to work with natural-pk tables.
The main disadvantage to using surrogate keys I’ve found is practical – apart from the obvious one that I have to ensure that a unique constraint is on the natural key (just have to be careful to do it) – but then these surrogate keys get propogated throughout my design (especially when I have modelled multiple inheritance with table-per-subclass, which is my personal preference) and writing queries and certain types of constraints can become tricky.
So far my impression of HTMLDB is that it is a nice simple product – but hopefully not so simplistic as that it requires surrogate keys everywhere?
Another problem that I’ll investigate is HTMLDB’s behaviour in regards to checking for record changes prior to updates; I’ve gotten the “can’t update because the hash values differ” error quite often when I’ve based a page on an updateable view. I use updateable views a lot as a way of hiding the database complexity from the application, and I don’t want to have to (a) denormalise my design just to suit HTMLDB, or (b) try to design HTMLDB to work with each entity separately.
I’ll post here results from some experiments I’m doing with Oracle. I want to explore the implications and possible benefits of mapping a truly relational logical database model directly to the physical model. In other words:
- Every entity mapped to a “base table”
- Every base table has one or more Unique keys (one arbitrarily chosen as “Primary”)
- No surrogate keys, except where the model doesn’t include any natural key
- All columns mandatory
- Application interfaces via fully updateable views (materialized or not)
In particular, I would like to determine how (if possible) to implement various types of table and database constraints apart from the standard PK and FK constraints.
Initial experiments indicate most of this is very possible, and with careful coding can be made to work. In fact, I’m sure some advantages over common practice will be demonstrated.
I’m doing all this on Oracle 10g Release 2 which I downloaded recently. I’ve installed it on my P4 with XP Home. For the frontend I’m using HtmlDB 1.6, although I’ll probably upgrade to HtmlDB 2.0 soon.
I should note that I don’t seriously expect this approach to have enough merit to be used in real projects – but this is not because of a lack in the relational model of data but because of the poor support of it by the SQL standard (and therefore, by DBMS’s that implement it). Therefore, I expect that to get all this to work I’ll have to write a lot of code very carefully, but that the resulting database will be very easy to write applications and queries for. For example, implementing all constraints on the database for a particular entity means that I don’t need complicated constraint checking code in every module that works with that entity.
I expect I may have to wrestle with row locking and handling constraint violations correctly in the presence of multiple users/sessions; I don’t want to have to enforce serialized access to get this to work because of the scalability problem.
I’ve found it interesting so far, anyway.
