Page 1 of 1

DB class. Design generic class to manipulate DB data

Posted: Tue Mar 21, 2006 7:51 am
by jmut
Hi,
Using php5.

I notice in my coding style that my sql queries are going all over the place.
I think it is about time to make things more structured when it comes to DB.

I want to ask you guys how you proceed when it comes to mangaing DB data.
1. Basicly it is about edit,delete,updated.
2. Do you make one generic class and extend it depending on table to manipulate.
3. How would you handle if data is across multiple tables.
4. What if you want to order by or whatever by some data (that is piled up from several tables.).
5. Is there any specific naming scheme used for table,database names that helps this process.
6. Are you always using full names in sql queries.

I have worked a bit with
http://pear.php.net/manual/en/package.d ... object.php

but not sure if this concept is the way to go.
Would like to hear your experience concerning this.

Posted: Tue Mar 21, 2006 12:09 pm
by Gambler
My POV is that relational databases are altogether outdated for web development purposes. Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
2. Do you make one generic class and extend it depending on table to manipulate.
I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.

For implementation details you can look up ActiveRecord information on the web. Although, it is simpler to download Rails and browse through it's code.

Posted: Tue Mar 21, 2006 3:34 pm
by timvw
Gambler wrote:My POV is that relational databases are altogether outdated for web development purposes.
Could you elaborate a bit on that POV? I still haven't seen a better model for datathan the relational model for data. So meaby i learn something new.

(If you were saying that most SQL dbms have deficiencies i can only agree. But i don't consider an SQL database a good representation of a relational database.)
Gambler wrote:
Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
- Imho most of the abstraction layers exist because there has never been a good standard for communication with sql databases. Imagine we needed abstraction layers in browsers so we could surf using ora-http and ms-http ;)

- Imho ORMs exist because people don't like to repeat the mapping work between OOP and SQL time after time..
(Not saying anything about the quality of those mappings.. Especially not the auto-generated ones by an ORM).

Afaik, the relational model was ment to be a model for data. I don't see why or how this is related to problems that are related to SQL.
Gambler wrote:
2. Do you make one generic class and extend it depending on table to manipulate.
I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.
I agree that there is no such thing as the perfect architecture and that it depends from case to case.

No matter how you implement your software, you will need to add the rules somewhere. I find it hard to understand how it would incure overhead if you decide to group those rules, because you have to write them anyway.
Gambler wrote:
For implementation details you can look up ActiveRecord information on the web. Although, it is simpler to download Rails and browse through it's code.
I believe Martin Fowler has done a great job explaining ActiveRecord and other models in his Patterns of Enterprise Application Architecture. Just don't forget that in most situations the truth is somewhere in between models (trying to get best of both worlds).

Re: DB class. Design generic class to manipulate DB data

Posted: Tue Mar 21, 2006 3:45 pm
by timvw
jmut wrote: I want to ask you guys how you proceed when it comes to mangaing DB data.
1. Basicly it is about edit,delete,updated.
So you need something to generate change-read-update and delete (crud) queries? How about a SQLGenerator? ;)
Btw, if you search the web for CRUD you'll find more resources :)
jmut wrote: 2. Do you make one generic class and extend it depending on table to manipulate.
Why would i extend from that table? Most of my tables do use different data and have different constraints. But that doesn't change the behaviour of my SQL table.
jmut wrote: 3. How would you handle if data is across multiple tables.
You could have a look at ADO.NET if you were looking for a possible implementation how you could represent that structure with OOP. In my experience that is a lot of overhead because i only want to generate queries, and for that task my SQLGenerator is more appropriate ;)
jmut wrote: 4. What if you want to order by or whatever by some data (that is piled up from several tables.).
Easy, my SQLGenerator accepts one or more pairs of (column name - asc | desc) that are used for the ORDER BY clause.
jmut wrote: 5. Is there any specific naming scheme used for table,database names that helps this process.
A decent SQLGenerator can handle all sorts of naming schemes. I remember that i had a couple of problems when i generated joins (because it required my generator to prepend the column name to the choosen columns in the SELECT clause).

jmut wrote: 6. Are you always using full names in sql queries.
No.
jmut wrote: I have worked a bit with http://pear.php.net/manual/en/package.d ... object.php
but not sure if this concept is the way to go.
Actually, the only one who can really judge is you ;) Try out a couple of different approaches and see how they do things better or worse than DBDO.

Posted: Tue Mar 21, 2006 4:11 pm
by Chris Corbyn
Gambler wrote:My POV is that relational databases are altogether outdated for web development purposes. Heck, they weren't ment for that in the first place. All those abstraction layers and ORMs are simply trying to mask the fact.
No idea what you're talking about I'm afraid :? Do you know something we don't? I'm sure that the object oriented databases, although in development have never been completed thus far.
Gambler wrote:
2. Do you make one generic class and extend it depending on table to manipulate.
I believe this is the idea behind "model" in MVC. However, on many occasions creating model is too big of a hassle. And it definitely incures some overhead.
It's not the idea I use in MVC. I simply have a wrapper for each database that contains the things I need. Taking it down to each table is a bit OTT in my opinion but each to their own :)

Posted: Tue Mar 21, 2006 4:58 pm
by Christopher
Responding to the original poster -- the first thing you want to do is create Model / Presentation layer separation. Building Model object to encapsulate your SQL is the solution to your problem. Within those classes, how you access data is a huge topic and as much personal preference as technical. Take a look at: Table Data Gateway (144), Row Data Gateway (152), Active Record (160), Data Mapper (165).

Posted: Wed Mar 22, 2006 2:17 pm
by Gambler
I still haven't seen a better model for datathan the relational model for data.
Through experementation I developed my own theoretical "model". But it's not strictly a data model, more like a bit of wishful thinking on the topic. The main idea is to move as much logic to application as possible. Thus, the "database" I would like to have would be mostly a set of tools for storage, manipulation and indexing rather than a stand-alone application. Some ideas I managed to implement:
1) Preservation and use of native types.
2) No schemas.
3) Purely functional/OO interface (no SQL).

If this would be a real database written in a low-level language I would go further:
- User-controlled indexing.
- Ability to store node pointers inside attributes.
- No separate client and server (akin to SQLite).
- Reduced operation overhead. Ideally, you could use DB interface the way you would use DOM without making your application too slow. Retrival of individual nodes should not be too costly to put inside a loop.
- Run-time caching control.

Posted: Wed Mar 22, 2006 3:41 pm
by timvw
Gambler wrote: Through experementation I developed my own theoretical "model". But it's not strictly a data model, more like a bit of wishful thinking on the topic.
You still haven't explained why the relational model is outdated for the web. Now i'm even more curious because it appears to me that your "model" is nothing more than a hierarchical model (tree-like structure). The same hierarchical model that people have left behind and replaced with implementations of the network model and the relational model.
Gambler wrote: The main idea is to move as much logic to application as possible. Thus, the "database" I would like to have would be mostly a set of tools for storage, manipulation and indexing rather than a stand-alone application. Some ideas I managed to implement:
1) Preservation and use of native types.
2) No schemas.
- In which context do you consider it to be native? MySQL data types? PHP data types? Operating system data types?
- How do you preserve consistency and safeguard constraints if you don't have a schema of your data?
Gambler wrote: If this would be a real database written in a low-level language I would go further:
You say that this is not a real database. How would you define it then?
Gambler wrote: - User-controlled indexing.
Imho indexing is simply a way to tell your dbms how you will use your data (and thus give it some hints in order to improve performance). I consider it as an implementation matter instead of a conceptual one.
Gambler wrote: - Ability to store node pointers inside attributes.
This gives me the impression that you would evolve from a hierarchical model to a network model. (A couple of decades ago it took a lot longer before people made that step. This gives me the impression that there will be a day that you evolve to a true relational model..)
Gambler wrote: - No separate client and server (akin to SQLite).
Again, an implementation detail.
Gambler wrote: - Reduced operation overhead. Ideally, you could use DB interface the way you would use DOM without making your application too slow. Retrival of individual nodes should not be too costly to put inside a loop.
I agree that a dbms should have acceptable performance. If i look at most available dbms out there today i can only conclude that a seperate server and client perform better.
Gambler wrote: - Run-time caching control.
Again, an implementation detail.

Posted: Wed Mar 22, 2006 5:17 pm
by Gambler
You still haven't explained why the relational model is outdated for the web.
Do you mean I should prove it? I don't think I have to. I said that it is my point of view, and this is truly so. Providing reasons and examples that would explain why it is the case would be lengthy and off-topic. If you're interested in discussion of various database models applied to web development, then I recommend you to create a separate thread, in which we will continue this conversation.

Posted: Wed Mar 22, 2006 5:31 pm
by Chris Corbyn
Gambler wrote:
You still haven't explained why the relational model is outdated for the web.
Do you mean I should prove it? I don't think I have to. I said that it is my point of view, and this is truly so. Providing reasons and examples that would explain why it is the case would be lengthy and off-topic. If you're interested in discussion of various database models applied to web development, then I recommend you to create a separate thread, in which we will continue this conversation.
No you don't have to prove it ;) You've just made an unjustified statement that's all. OK so someone can make a thread to discuss it, except then it makes the OP of the new thread look wrong since they're going to have to ask why relational databases are outdated. Hmm... I don't care either way because they're not outdated, they're actually the best we have at the moment.

As for what you describe Gambler with the whole database model being directly integrated to the application and all OO it's already in development, though not publicised massively thus yet.