Database views

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Database views

Post by alex.barylski »

I always thought Views were Stored procedures...I was just introduced to Views and they seem to essentially be a phrasebook pattern.

Instead of hammering out a complex query in the code you invoke a View instead.

I'm not sure I like the idea or dislike the idea yet, but I find the solution interesting. Certainly if one could eliminate SQL from their code almost entirely, that would be a bonus as it's one thing I hate doing.

This after spending the last few weeks implementing a data mapper, etc...

opinions?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Database views

Post by josh »

Views don't eliminate the design problem datamappers solve. We should probably team up or something, my data mapper has come quite a ways as well. But yeah views can eliminate the need for some complex SQL in some cases
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Database views

Post by alex.barylski »

I guess my question really was, what are the benefits to using a DB view as opposed to using phrasebook pattern?

Essentially you store the SQL as prepared statements in an external file and use a lookup function to fetch the SQL and pass the query string to be evaluated.

Views are probably cached, whereas a query lookup in phrasebook is not possible, at least to that degree.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Database views

Post by josh »

Well yeah I don't know crap about phrasebooks really, but basically the way I think views differ is that they're updated when their owning tables are updated. If that's the case I'd assume there's a performance penalty at write time but a benefit at read time.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Database views

Post by alex.barylski »

From what I can tell...views offer greater performance. Because they are stored at the DBS level far more advanced caching can occur.

I'm not convinced they are a best practice but under certain circumstances they may be.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Database views

Post by josh »

It definitely shouldn't be your primary approach to structuring business logic, but is definitely better then representing business logic as SQL which Codd himself admitted is flawed.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Database views

Post by alex.barylski »

I don't think business logic can existing in a View, can it?

From what I can tell, a view is simply a SQL stored in a centralized location, whereas stored procedures one might implement business logic, which I definetely disagree with.

I was familiar with stored procs, but views were totally new to me until now. I always thought they were a conceptual technique, much like phrasebook.

What interests me most about views, is my OQL is somewhat limited due to the expressive capacity of a OO fluent interface. There are some instances where explicit SQL 'might' be required in which case it would be neat if I could write a view and invoke the view using OQL.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Database views

Post by josh »

I don't know what the capabilities of views are exactly, but I guess I mean don't encode business logic in the view definition. See thread where I debate stored procedures for my stance ( its all about shape vs behavior ). Basically your behavior should not be implemented as views.
wei
Forum Contributor
Posts: 140
Joined: Wed Jul 12, 2006 12:18 am

Re: Database views

Post by wei »

Post Reply