Re: Why bother with Logical data model?

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:1155127722.826576.24760_at_i3g2000cwc.googlegroups.com...
> David Cressey wrote:
>> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
>> news:2tjCg.4506$o27.308_at_newssvr21.news.prodigy.com...
>> > Why? Because it saves time--perhaps not initially, but definitely in
>> > the
>> > long run. In most of the databases I've worked with there is a
>> > disparity
>> > between the logical model and the physical model. The logical model
>> should
>> > at a minimum be in BCNF, but if possible, 5NF, but that is not always
>> > true
>> > in the physical model. Sometimes tables must be split for valid
>> > business
>> > reasons, such as security. Sometimes, though seldom, a denormalized
>> > table
>> > performs better--at least for the particular application where it is
>> > most
>> > used. The logical model should remain independent of such
>> > considerations,
>> > so that you can focus on the structure of the data that is to be stored
>>
>> The above is a very, very good point. It deserves careful attention by
>> most
>> of the readers of this newsgroup.
>>
>
> I disagree. It is the logical / physical confusion at work again.
> Normalization applies only at the logical level (relations or tables)
> whereas Brian's reply assumes that tables (and therefore normalization)
> are also the building blocks of the physical model (implementation and
> storage).
>

I don't agree with your characterization of confusion, nor your definition of the physical model. The logical model should not depend on any particular implementation; however, the choice of a particular database engine can affect the layout of tables. Each database engine has limits: the number of columns needed may exceed the limits of the engine; the size of a row may exceed those limits. In addition, each database has features that can affect the layout of tables. For example, the need for surrogates depends partly on the engine selected: Oracle, DB2 and SQLBase each have a FOR EACH ROW trigger that can be used to correlate rows in successive database states; Sql Server does not, but you can use surrogates to meet that need. The point is, the layout of the tables deployed can exhibit marked differences that depend only on the choice of a particular database engine. I don't consider the physical layout on storage to be part of any database model. The choice of RAID-5 or RAID-0+1 has performance implications, but does not impact the model; locating an index on different physical media has performance implications, but does not impact the model. On the other hand, the decision to use a partitioned view to improve performance does affect the physical model because additional database objects are needed (tables and views)--not because the underlying tables and indexes will reside on different storage media.

> In principle the logical model is supposed to be the level at which
> users and applications interract with the DBMS. In Brian's view
> however, the logical model presumably exists largely in a design
> document or in the designer's head. Obviously this confusion arises
> because SQL DBMSs commonly use the CREATE TABLE statement as the main
> method or even the only method to define the physical model as well as
> the logical one. The Table = Physical assumption is therefore a painful
> necessity for many SQL users. Readers of this newsgroup should not be
> misled about the real difference though.
>
> Further to what Brian said. If the logical model has to be denormalized
> or otherwise compromised in the interests of making physical changes
> then you should consider applying sufficient constraints to maintain
> the integrity features of the original version.
>

Absolutely. In addition, a compromise of the logical model can limit the database engine's ability to enforce constraints, relegating that task to the applications. This fact must be carefully considered before choosing to compromise. It may be cheaper in the long run to just throw hardware at the problem.

> --
> David Portas
>
Received on Thu Aug 10 2006 - 07:42:17 CEST