Re: sql views for denomalizing

Eric Junkermann wrote:
> In message <1122996168.609599.96180_at_g44g2000cwa.googlegroups.com>, dawn
> <dawnwolthuis_at_gmail.com> writes
> <snip/>
> >Beauty is in the eye of the beholder. Think of a web page with a list
> >of students in a class. Click on a student and you see all of their
> >classes with the attributes of each class. Click on the class you came
> >from and you are back to that first list. There is no need for an
> >intermediate web page to cross references these two. They can store
> >all of their references without having helper tables to do so. From a
> >logical modeling perspective, this is much more elegant, don't you
> >think?
> <snip/>
>
> I gave up on an earlier thread because my brain refused on that occasion
> to cope with answering things point by point, but that paragraph is a
> perfect illustration of what I mean.
>
> The user interface described is clear and simple, I like it.
>
> But then you start talking about storage, which has nothing whatever to
> do with interface design, and neither has anything to do with logical
> modelling.

I'm glad you piped up on that point because I hear hints of similar perspectives from others and I haven't caught on to what the point is as yet. I'll try to explain better what I'm thinking. Then if you have the patience, I would really appreciate trying to determine whether we agree, but with different terminology, or whether we disagree, in which case I need to understand your point better.

Much of what I do if I am writing data-intensive business application software has to do with data models. Software is all about data models. The connection I'm trying to make between a UI and a DB is not that the same instance of a data model should head to each -- I'm not looking to couple any two services that use data models to eventually interface with a device. What I would like is the same modeling approach, techniques, tools, schema, naming conventions, languages, api in each case.

Here is a list of some possible Input and/or Output "devices": DVD
Magnetic tape
PC monitor
Printer
Hard disk
Paper Tape Device
External server
Memory
Cell Phone

Let's look at the "controller" part of a software application that I'm writing. I'm not going to interface with any of these devices directly -- likely with services that employ services, that use services,... Each service with which this controller software interacts takes in and/or gives back a (subset of an entire) data model, plus corresponding instance(s) of data.

Think of a hub and spokes architecture where this controller software works with various device services.

I want the api (as far as the data model and instances are concerned) to be the same for each of these device services. I don't have to care whether data are headed to a disk or on a DVD, or a UI on a screen as long as I know, where, whether & when data need to be vaidated, how a message from one devices needs to tranform into a message to another, and whether I'm reading or writing, for example.

The Paper tape representation of a tree structure will surely be different than a mag tape representation, but the controller software only needs to work with a data model & related metadata, passing to and from device services of varying devices.

The device services would need the logic for representing the data as they like to "see" it -- their unique representation, and the model would include metadata to help it make such decisions.

I do not want to model a tree one way when I'm working with one I-O device service and another with others. That is what typically happens today if writing code in Java to a web page UI and a to a SQL-DBMS. The metadata, naming conventions, and even data modeling techniques are different, and these differences buy me nothing.

> The beautiful user interface does not own the data, even if it is the
> only current use of it.

Agreed. Bobbie, the owner, owns the data, whether it be in a paper file folder, a document that has the minutes to a meeting, a screen shot frozen for a powerpoint slide, or a database stored on a CD. The software apps have the responsibility of getting the right view of the data (i.e. data model + instances) to the right place at the right time.

> As soon as you reflect your current presentation of the data in the
> logical and physical data design (especially if they are effectively the
> same), you have put a considerable barrier in the way of using the same
> data in some other way.

You can use the word "presentation" for data headed to a UI and something else for data headed to a db, but in either case a middle tier software service should only need to pass a model/view, including metadata + data instance(s) to the device service and that service (or a service it uses) can determine the representation and pass that along for another service to do the rendering. That device service (whether related to a browser page on a PC or a table stored on a DVD) would need to know how to do the representation of such a view for that device.

I'm sure I will choose a different instance of a subset of an overall data model, with different attributes in the view, for a single web page or a single db transaction. But I don't want to think of modeling data and the languages/api for data model/schema/instance(s) for one I-O device service as so decidedly different from another.

> Your logical model will be difficult to extend,
> and your physical design will have a high probability of performing
> badly for the new uses, or even for the same use as relative volumes of
> different data items change.
>
> Where has all the elegance gone?

That is my question. Everything insists on its own api for data it will work with. That proprietary way of working with the data can be under the covers, but I want the apis to be the same (or at least a lot more similar than now) for each such service.

> The logical data model should be about the data and only about the data.

Yes, yes, yes! I definitely agree with that. That is my point. If it is just about the data, then why would the modeling tools, techniques, approaches, etc have to be so different if modeling data destined for a PC monitor or a piece of rewritable paper or a disk? There is no reason they cannot all take in and pass back data in the same way.

> The physical design should be about finding an acceptable performance
> level for the logical operations that are currently performed.

yup, agreed.

> There is no problem in having a structure clash between the logical
> model and the presentation of the data in a user interface,

agreed. It is the api to the service that does the presentation that could be the same as the api to the service that does the representation of the same model in to be stored on a disk.

> that is what
> programming is about.
>
> The programmer's view of data is a short-sighted one,

them's fightin' words, my friend. I could say that someone managing web server software or dbms software each has a very narrow view of the data. Sometimes they even think that there is no other data model api than the ones they provide. It is the software developer that sees that we are modeling data everywhere (and that we should stop doing it so very differently everywhere).

> they should not be
> allowed to control the data model.

That was the clearest statement to help me understand where there are gaps between what I'm saying and what you (and maybe others?) are hearing. It sounds like you really think that data models are only for dbms products and not used all throughout software. Is that your real stance or do you want to withdraw that?

Thanks for your help -- I have definitely been enlighted (even if not at all happy with what I'm hearing). Cheers! --dawn

> Eric
> --
> Eric Junkermann
Received on Thu Aug 04 2005 - 03:42:50 CEST