Page 1 of 2

SQL dependencies

Posted: Sat Jan 13, 2007 1:08 pm
by alex.barylski
I've been experimenting lately with different approaches in dealing with SQL.

For a long while I disliked the use of objects or even classes (statically called) wrapping DB functionality as I found the mismatch between relational data and class grouping more confusing then benefital so I stuck strictly to functions.

I've played with ORM investigated my own design/implementation and have concluded (as of now anyways) I dislike it. I like the idea of keeping my tables bound to a single class/object so long as all that object does is work on the table it maps. When working in a relational model such as that offered by RDBMS like MySQL you often find that relational theory points you in one direction while OOP points you in another.

When joining tables, using objects I find I often end up with convoluted code, with objects depending on other tables because relational theory tells you thats best practice (DB normalization) while this very effect is frowned upon when working with OOP.

Having favoured OOP & good design over performance I started using static methods inside classes. Startinging off as basic CRUD operations I then experimented with trivial queries.

This approach took me in the direction of using PHP to specify relationships rather than depending on SQL, which had the bonus of keeping dependencies at a minimum, wrapping tables with a class to keep things organized and yea, tying all the peices togather using PHP rather than SQL. So far (nothing overly complicated) I've been pleasantly surprised. While this works against using a RDBMS (maybe I should switch to FoxPro or non-relational storage) like I said, I've been more than happy not having to keep track of SQL dependencies - I find it much easier to work in PHP.

Some might argue this is a sign of not planning a schema well enough and thus is the reason for my volatile SQL and problems with intra-table dependencies. In my experience it's the data model which is the most volatile of all components of a system, so why not and reduce headaches in that area?

Like I said, so far it's been pretty trivial SQL dependencies, so this might not even work in all situations, for instance:

When creating a entertainment web site which lists bands, tour dates, etc each which rely on the other....and performance is impetus, maybe using SQL would be justified. But in my applications where the user base is limited it makes sense to express table relationships using PHP rather than SQL...

Not sure what my intention is with this thread...we'll see what happens :)

Thoughts?

Posted: Sat Jan 13, 2007 5:52 pm
by Christopher
Perhaps you could show us some code examples so we could figure what the heck you are talking about and thereby give our thoughts?

Posted: Sat Jan 13, 2007 7:05 pm
by Ambush Commander
I think that you should try (really) using a Data Mapper. From what I understand (and not very much), your still wed to the Table Data Gateway method, which while simple, doesn't scale well with complexity. ORM doesn't have to do much with it: your in-memory objects do not (and usually won't) correspond with the database schema.

Posted: Sat Jan 13, 2007 7:58 pm
by alex.barylski
Basically using imperative programming to explicitly express relationships instead of declarative SQL so I can better reuse and more importantly organize CRUD type classes, etc...

Yes I'm going against relational theory best practice and possibly *not* normalizing a DB to a level of perfection but favouring KISS over speed is what I prefer most of the time (actually lately - all of the time).

How do you organize your multiple table SELECT's when class A maps table A and class B maps table B...

A query like:

Code: Select all

SELECT * FROM a,b WHERE a.id = b.id AND a.name = "Freddy"
The code is dependant on both tables so which class does it fall under? Some people might say throw it in a special SELECT-er class - but I dislike that idea...

My goal is to keep the schema simple enough or investigate new techniques to allow *almost* complete table independance - perhaps an impossible goal but I have never been one to believe in the (im)possible. :P

When I want to reuse class A in another project I want to simply create the table and copy the class and not have to find a SELECT-er class and locate the one method which is needed.

Cheers :)

Posted: Sat Jan 13, 2007 8:08 pm
by Ambush Commander
If your example SQL is correct, tables a and b should be merged together, as they are both dependent on the same primary key.

If there is a specific, 1 to 1 or 1 to many relationship between classes a and b, it usually is expressed with a foreign key mapping. Let's consider a different example:

Code: Select all

SELECT * FROM a,b WHERE a.id = b.parent_id AND a.name = "Freddy"
If performance wasn't an issue, this could have been composed into two SQL queries:

Code: Select all

SELECT * FROM a WHERE a.name = "Freddy"

Code: Select all

SELECT * FROM b WHERE b.parent_id = $a_id
...clearly separating the two objects and their mappings. If you reuse b, it doesn't need to know about a, just a_id.

If you insist on using a JOIN, yes, you have to put the query function in a specific object, either the one associated with a or b. You have a choice between the class that usually loads the objects (in this case a) or the item that's being returned by the SQL query (b).

Posted: Sat Jan 13, 2007 8:20 pm
by alex.barylski
Hey Ambush that was an error, they do not share the same primary key :P

1:n relationship :)

Anyways, you've nailed the hammer on the head...that is exactly what I have been doing...keeping the classes seperate...using PHP to call one API passing the ID's of one table to another class and returning a result that way...

Obviously, it's not using relational power of a RDBMS and performance suffers...but dependencies are minimized and organization is made clearer - so I'm happy...

Any other trials and tribulations you may have experienced in using this technique other than performance being a potential issue?

Have you ever investigated an solution/implementation similar to ORM, etc which allowed SELECT queries of arbitrary complexity?

Posted: Sat Jan 13, 2007 8:29 pm
by Ambush Commander
Hey Ambush that was an error, they do not share the same primary key Razz

1:n relationship
I suspected as much. Using id in that manner is misleading and I don't recommend it.
Any other trials and tribulations you may have experienced in using this technique other than performance being a potential issue?
Well, I don't use merges very much (never quite understood the syntax :oops:). But from your description, I suspect there's quite a bit of code duplication inside the code that calls your SQL classes, because they have to do all the passing around themselves.
Have you ever investigated an solution/implementation similar to ORM, etc which allowed SELECT queries of arbitrary complexity?
Hmm... I've never had so many multi-table retrieval operations that I had to formally organized them. I just add them on an ad-hoc basis to the classes that make the most sense. There couldn't possibly be that many relationships.

Posted: Sat Jan 13, 2007 8:59 pm
by alex.barylski
Well, I don't use merges very much
Mergers?? :?
But from your description, I suspect there's quite a bit of code duplication inside the code that calls your SQL classes, because they have to do all the passing around themselves.
I don't think so...but maybe...how do you mean? Duplicated SQL or PHP? Can you give an example?
Hmm... I've never had so many multi-table retrieval operations that I had to formally organized them. I just add them on an ad-hoc basis to the classes that make the most sense. There couldn't possibly be that many relationships.
Ermmm...admittedly...neither do I...usually...but in this case it's a custom built enterprise application which hosts CRM, KBASE, CMS, CMMS, and so on...many of the apps communicate and therefore the potential for multiple table queries is endless...

being in house, the performance issue isn't a big deal...

Cheers :)

Posted: Sat Jan 13, 2007 9:11 pm
by Ambush Commander
Mergers??
Joins, I mean. :lol:
I don't think so...but maybe...how do you mean? Duplicated SQL or PHP? Can you give an example?
PHP. Example:

Code: Select all

// okay, I want to find all fruits in the basket
$Basket = $BasketMapper->findByName($basket_name);
$Fruits = $FruitMapper->findAllByBasketID($Basket->id);
Done whenever you want to get the fruits. Since it's a one to many, where the fruits clearly are aggregated by the basket, it would make more since to put the fruits inside the basket:

Code: Select all

// okay, I want to find all fruits in the basket
$Basket = $BasketMapper->findByName($basket_name);
// $BasketMapper already loaded the fruits into member variable $Basket->Fruits
Lazy loading if necessary.
Ermmm...admittedly...neither do I...usually...but in this case it's a custom built enterprise application which hosts CRM, KBASE, CMS, CMMS, and so on...many of the apps communicate and therefore the potential for multiple table queries is endless...
Good god! An uber-application. Here's what I think: as you have more and more tables, you need more and more classes to handle the tables. More code is inevitable. But while the number of relationships potentially rises exponentially with each new class of objects, in practice, models are not so interconnected.

I would recommend keeping each of the applications largely separate, as you have been trying to do. If you need to mash-up two of the applications, think about whether or not the mashup would be more appropriate in just one of the applications, or given it's own domain.

Judicious use of inheritance, polymorphism and composition can also improve reusability: if you've got a Basket class, and in one app it holds fruits and in another it holds shopping items, abstract the interface, and use the above-mentioned techniques to adapt it into each case. A class won't be perfectly reusable the first time you write it, and that's okay:

The first time you do something, do it.
The second time you do something, cringe but do it.
The third time you do something, refactor!

Posted: Sat Jan 13, 2007 11:05 pm
by Christopher
I think you should just build cestom Model classes and let reuse happen when it does -- reuse is a sort of wishful thinking in the domain layer anyway...

Posted: Sun Jan 14, 2007 4:59 am
by wei
don't forget to use database views to simplify your queries, very useful if you just want to pull out some data that have complex joins. Views can be like denormalized tables. the down side is, of course, that you can't save/update views. Then, you may try a simple active records for each of the table (for simple crud) and views (for complex queries).

Posted: Sun Jan 14, 2007 11:37 am
by alex.barylski
AC wrote:PHP. Example

Code: Select all

// okay, I want to find all fruits in the basket 
$Basket = $BasketMapper->findByName($basket_name); 
$Fruits = $FruitMapper->findAllByBasketID($Basket->id);

// okay, I want to find all fruits in the basket 
$Basket = $BasketMapper->findByName($basket_name); 
// $BasketMapper already loaded the fruits into member variable $Basket->Fruits
While it is obvious there are two steps required to pull the result instead of one...I fail to see how this is "duplicate" code? :?
AC wrote:Done whenever you want to get the fruits. Since it's a one to many, where the fruits clearly are aggregated by the basket, it would make more sense to put the fruits inside the basket
I'm missing something...but I think you are suggesting I use composition? I'm not familiar with Lazy loading... :)

The problem I see with that...is the $BasketMapper is now dependent on $Fruits which is fine...but I think were looking at the problem from different levels of abstraction. These classes I have act as the DAL - the API communicate directly with the RDBMS. There is no data mapper per se. They are not objects but static methods so they do not map the table like Data Mapper or ORM. The API is best wrapped later on if higher level of abstraction is desired.
arborint wrote:I think you should just build cestom Model classes and let reuse happen when it does -- reuse is a sort of wishful thinking in the domain layer anyway...
It's not just reuse I'm after but removal of dependencies, clearer more organized code as well...again...I think were looking at the problem from different levels of abstraction...
wei wrote:don't forget to use database views to simplify your queries, very useful if you just want to pull out some data that have complex joins. Views can be like denormalized tables. the down side is, of course, that you can't save/update views. Then, you may try a simple active records for each of the table (for simple crud) and views (for complex queries).
the idea is to not heavily depend on SQL though....it's hard to explain without going in great detail... :)

Cheers :)

Posted: Sun Jan 14, 2007 1:16 pm
by Christopher
Hockey wrote:It's not just reuse I'm after but removal of dependencies, clearer more organized code as well...again...I think were looking at the problem from different levels of abstraction...
I don't know what is clearer and more organized than clearly putting all the depencencies necessary in a single custom Model class. No class hierarchy, practically self documenting, complete flexiblity as to internal implementation...

Posted: Sun Jan 14, 2007 1:27 pm
by alex.barylski
arborint wrote:
Hockey wrote:It's not just reuse I'm after but removal of dependencies, clearer more organized code as well...again...I think were looking at the problem from different levels of abstraction...
I don't know what is clearer and more organized than clearly putting all the depencencies necessary in a single custom Model class. No class hierarchy, practically self documenting, complete flexiblity as to internal implementation...
By dependencies...in a single custom model class...you mean...keeping multiple table SELECT's in a single class?

Like I said....different levels of abstraction...you appear to be one level up...I'm thinking DAL and you guys are thinking Model...

Cheers :)

Posted: Sun Jan 14, 2007 6:12 pm
by Ambush Commander
While it is obvious there are two steps required to pull the result instead of one...I fail to see how this is "duplicate" code?
Admittedly, the transgression is weak in this example. But if you needed to change something in the $FruitMapper, you'd be running around to wherever you put this code to do the change. For more complex queries (which you seem to be avoiding for now), this could be several lines of query code to get to the stuff you want.
These classes I have act as the DAL - the API communicate directly with the RDBMS. There is no data mapper per se. They are not objects but static methods so they do not map the table like Data Mapper or ORM. The API is best wrapped later on if higher level of abstraction is desired.
Sooner or later you'll spend more energy organizing these direct database calls than if you reorganized into wrappers.
It's not just reuse I'm after but removal of dependencies, clearer more organized code as well...again...I think were looking at the problem from different levels of abstraction...
Okay, here's the thing: sometimes there are required dependencies. It makes no sense for a clock widget not to have a dependency on some time mechanism. It makes no sense for a FruitMapper not to have a dependency on the Fruit object, which it creates. You can't "remove" the dependencies, but you can make them easier to move around by using a separated interface.
Like I said....different levels of abstraction...you appear to be one level up...I'm thinking DAL and you guys are thinking Model...
So maybe we're trying to tell you that you should abstract more to get the sort of clean dependencies you're looking for.