Jump to content
Larry Ullman's Book Forums

Approaching The Dbms. Tips, Tricks And Tools For The Trade


Antonio Conte
 Share

Recommended Posts

Hey, everyone.

 

Please note that this thread is meant as a general purpose discussion thread. My thoughts are merely meant to get the discussion going.

 

I want to create a general discussion thread about working with a database system from a programming standpoint. The thread is intended for discussing tools for abstracting the process of doing CRUD-operations against a database. I'm talking about Active Records, Wrapper Classes and Database Helpers. Please feel to talk about what you like here.

  • Upvote 2
Link to comment
Share on other sites

Good thread, Antonio. Thanks for offering up all these points to discuss.

While I still have a lot to learn about DBs, here's my general opinion:

 

When I first started using RDBMSs, joins were tricky. I didn't like them because they didn't make sense and they seemed really inefficient.

Furthermore, like you stated, somehow, a more object-oriented (programming-like) structure for handling data just made more sense.

As such, I slowly and begrudgingly learned how to design databases, always taking everything with a grain of salt and being rather skeptical about the whole thing.

 

However, after having more and more hands on experience with DBs, I've come to realize that for 99.999% of sites out there (i.e., unless you're huge like Google, Facebook, etc.), a standard relational DB is the best way to go.

RDBMSs are incredibly optimized, and as far as I can tell, there is no more efficient way in general to store massive amounts of data and keep it organized.

Furthermore, with proper indexing, etc., things will be lightning fast, even with millions of records in a DB.

 

So, while I acknowledge that your comments were merely intended to drum up a conversation, I think that a lot of the comments feel like the types of things that people who don't truly understand DBs (i.e., myself a couple years back) would say.

 

Anyway, that's obviously not an insult on you, Antonio, as I fully acknowledge the purpose of this thread, but basically, yeah, I think DBs are the best solution for a vast majority of websites, and NoSQL solutions like MongoDB only really benefit a very select number of (huge) online businesses.

Link to comment
Share on other sites

Good post, Jon. As stated, I don't like relational databases from a programming standpoint. I was wondering what people here do to make their lifes easier in this regard, and not trying to say relation DBs are a bad/slow thing.

 

My latest experience come from CodeIgniters active records class, which I hate. I've on the other hand heard great things about an Orm called RedBeam PHP. I have also developed several DB wrappers over the years myself to make working with relational DBs easier to work with.

 

Thanks for a great post, Jon. Hope you'll answer this one as well.

 

I've updated the original post to clearify some points.

Link to comment
Share on other sites

Its nice to have a talk about relational databases but anyhow we need to build our sites into a stable environment framework etc, so what kind of framework would support such a method? Your idea's are cool but we will make more progress if we make do with what is available. How are you even going to get people on a website with all the competition these days. Everyone is doing SEO and its getting much harder to get ranked next to big competitors. I really think this kind of database efficiency is something we should consider talking about down the road if we have a high traffic website. My advise you to you Antonio is "Get your web site Build"!

Link to comment
Share on other sites

You are not getting the point here, guys. I am not seeking help solving actual problems. I'm wondering if anyone of you do anything to make your life easier?

 

As an example, my DB wrappers allow you to group by a column. This mean I can get structures like these very simple:

 



$result = DB::fetch_array('players', 'position'); // Returns array grouped by the column "position"
$result = DB::fetch_objects('players', 'nation'); // Returns array of objects grouped by nation

array(
"keeper" => array(
1 => array( "name" => 'Gianluigi Buffon', "country" => 'Italy', ' ),
	 13 => array( "name" => 'Marco Storari', "country" => 'Italy', ' ),
 ),
"defender" => array(
3 => array( "name" => 'Giorgio Chiellini', "country" => 'Italy', ' ),
 6 => array( "name" => 'Leonardo Bonucci', "country" => 'Italy', ' ),
 ),
);

 

The same wrapper will also return a single result array when only one row is returned. Usually, you need to pop from the array or use $result[0], which, again, I hate. My wrapper also build an internal result array. This means I can iterate trough the result several times if I need to. It also has internal escaping, so no need to worry about that.

 

Starting to get me here? The thread is meant to help others discover tools for the job, not how to actually do the job itself. :)

 

Regarding the Framework discussion, Edward, you could extend the framework with your own libraries or helpers. YII ships with a query builder, but you don't actually have to use it if you don't like it. The models should help you interact with your DB, but how that is done is up to you how to implement.

Link to comment
Share on other sites

Yeah i guess you can build anything you want into the framework but for starters, we have to see if we can even get our idea to work before we work on its performance. Like i said there are 1000's of these Web Sites up now, and everyone can write the same code, so why would any of our idea's work so easily? If i had a Web Site with high traffic, i would be happy to discuss this with you in more details, but quite frankly if I was to discuss such features I would just be talking in the air. You remember you said to me about my project don't worry about blocking swear words and stuff now, well later on I came to realize that you are right, those kind of features can be implemented at any time, the most important point now is to get a working model up and see what can be improved for the final. Do you think Mark at Facebook was thinking about these kind of database features when he built version 1? Anyway no harm in your thread but we have to think more about the reality of the situation and what ideas are actually feasible for the present time.

Link to comment
Share on other sites

Edward, your comments don't make a lot of sense to me in the context of this:

 

I want to create a general discussion thread about working with a database system from a programming standpoint. The thread is intended for discussing tools for abstracting the process of doing CRUD-operations against a database. I'm talking about Active Records, Wrapper Classes and Database Helpers.

 

Can you expand on how you think ORMs, active records, wrapper classes, database helpers prevent quick and efficient development? Or how this is only relevant to high traffic websites?

 

I'm also not following your point:

 

Anyway no harm in your thread but we have to think more about the reality of the situation and what ideas are actually feasible for the present time.

 

... in the context of Antonio's statement above.

Link to comment
Share on other sites

Hey, Antonio. Sorry for the delayed response.

Looking at your edited original post, the post I made after that no longer makes much sense, but anyway, here's what I generally do to make DBs easier to deal with from PHP:

 

- As we have discussed before, I'm not much of a framework guy. I instead like to write my own helper functions, which are best optimized for the task at hand and depending on what needs to be accomplished for the specific site I'm working on.

 

- I used prepared statements for everything these days, as they provide more security and can be faster in some situations.

 

- Related to the above point, I like to write functions that can take all the necessary parameters for handling prepared statement queries, and then process everything accordingly.

For example, I generally write a function that can return results for any basic SELECT statements that don't require joins. The provided arguments are generally two arrays and a string. One array is a list of the inbound parameter names (as used in the DB) and the values I want to use for those DB values. (This essentially equates to the WHERE clause in a SELECT statement.) The other array is a list of the outbound values to map the results to, and the string is the name of the table to run the search on.

The results set is then return as an array and stored in an array variable accordingly. Of course, this only works for SELECT statements without any joins, but nevertheless, that serves a vast majority of my queries.

 

- Similar to the above, I will also write functions for simplifying UPDATE and INSERT statements as well. Also, I will write functions for handling certain joins as necessary. Generally, all these functions strike a balance between being adaptable for any project I may want them for and being customized for the specific project I'm working on at the moment, so that they are more efficient than the super generic functions you see in most frameworks.

 

- I quite often denormalize DBs. This is what I call the "common sense check" after the 3NF has been followed to create a DB. It's hard to describe how I do this step, but really, with a little common sense and some experience in dealing with DBs, this is not a hard thing to do, and it often makes things a lot more efficient without compromising integrity.

 

- I use phpMyAdmin for all DB operations. This isn't really a "trick", as I imagine most people are the same as me in this regard, but a DB interface like phpMyAdmin greatly simplifies all DB operations.

 

Well, that's all I can think of at the moment. As this discussion continues, I might think of some others as well.

Also, Edward, you might want to avoid making such general, sweeping statements, especially without more concrete facts to back up your claims. Also, as Antonio stated, I think you may have kinda missed the point of his original post, which he has since edited to better reflect what he wanted to say.

Link to comment
Share on other sites

Edward, your comments don't make a lot of sense to me in the context of this:

 

 

 

Can you expand on how you think ORMs, active records, wrapper classes, database helpers prevent quick and efficient development? Or how this is only relevant to high traffic websites?

 

I'm also not following your point:

 

 

 

... in the context of Antonio's statement above.

 

Yeah because he edited his post and cut out about 15 lines. Earlier the post was more on improving database performance. Thanks Hartley i will make any statements I like so long as I am not offending others - this is an open board.

Link to comment
Share on other sites

Hey, everyone.

 

Please note that this thread is meant as a general purpose discussion thread. My thoughts are merely meant to get the discussion going.

 

I want to create a general discussion thread about working with a database system from a programming standpoint. The thread is intended for discussing tools for abstracting the process of doing CRUD-operations against a database. I'm talking about Active Records, Wrapper Classes and Database Helpers. Please feel to talk about what you like here.

 

Since you question has changed, well if i was doing what you were saying, i would write a general database helper class then i would extend this and override the parent class to work the basic crud features with MySQL. I would split up the main features of on the website into their own separate classes for User, Authentication, URL, Template, Product, Category etc.

Link to comment
Share on other sites

It has never been about database performance. I talked about relative performance considering I needed four joins to get four strings. The example is an illustration of how annoying assembling data that has been normalized can be. Of course, these data has relevance elsewhere, so it's not a bad solution. Just very annoying sometimes. It was a border land example — a worst case scenario to make an example of why I hate working with relational databases. The example was supposed to illustrate the difference between how you really use the data and how you save it in a DB.

 

To conclude. DBMS systems don't really let you save your data the same way you use them. However, you can make your life easier by using some tools, like the Active Record pattern, including ORMs, creating wrappers to make this easier, or even switch to NoSQL. (And, I don't mean to direct anyone towards that. - But someone may have expirence to share with others).

Link to comment
Share on other sites

Just to add a thought (albeit perhaps not an accurate one), I was thinking about DBs a bit more, and it seems to me that the traditional RDB model (when following 3NF) is optimized for INSERT and UPDATE statements, because the RDB model is very good at preserving the integrity of data (and that's really the point of RDBs, right?).

 

On the other hand, NoSQL solutions, which quite often seem to put data in a more accessible format for programming languages (e.g., JSON format), seem to be optimized very much for SELECT statements, although, they make you (the programmer) have to be more responsible for maintaining the integrity of the data (which is sometimes no small task).

 

So, for example, if your site is almost all SELECT statements, then maybe a NoSQL solution would be better.

 

However, I also think that there are so many factors involved with high-end DB systems that it's ultimately impossible to predict the outcome of a particular data set and which solution is best without extensive testing.

 

Of course, I've never had access to amount of data that the Facebooks of the world have to deal with, but I've dealt with some pretty big data sets, and even when performing sometimes multiple joins per query, it's so flipping lightning fast (it really is incredible how fast RDBMSs are), that you really don't notice the extra few milliseconds that the RDBMS solution takes over the NoSQL solution.

 

Again, I'm not trying to argue in favor of RDBMS solutions, but to a degree, I think NoSQL has become a recent buzzword in the industry (can anyone say "cloud computing"?!), and people are quick to dismiss the power, robustness and reliability of a traditional RDBMS system. NoSQL has its place, but that place is a place that most of us will never reach, I think.

 

Again, I'm not saying RDBMSs are the greatest thing ever and that they will always be a better solution, but they've been around as an established method in the computing industry for almost 50 years for a reason. How many other technologies do you know that you can say have been around for 50 years and remained relatively untouched?

 

To end this post, I'm not trying to argue one side or the other, but I do think that there are a lot of misconceptions surrounding DBs, and I just wanted to share my thoughts.

 

Guys like Antonio have way more knowledge and experience with DBs than me though, so I bow down to their opinions anytime and any criticisms they may have of this particular post.

Link to comment
Share on other sites

I agree with every word, Jon.

 

I'm not really interested in NoSQL because of speed or "scaling", that is the buzzword for NoSQL. It's not really those things that interest me, as the project in question is intended for fewer than 10 peoples. I'm interested in NoSQL, and MongoDB in particular, for the sake of learning and testing. Small projects are good for testing new things out.

Link to comment
Share on other sites

Seems we are starting to get on the right track now. :)

 

Seems like a lot of you use YII, or at least will consider using it with the new release. What do you think of YII's Active Record? Do you use that our the queryBuilder in your projects? What do you like/dislike it for?

 

Have been working with CodeIgniter for a couple of months now. Still loath their "active record" implementation. Will be good to get this project released and never touch that again. It's not that it's not working. I just don't like how you're supposed to use the damn thing. #first-world-problems

Link to comment
Share on other sites

Active records is a pattern where you map values to the object's properties. One row in a table/view will correspond to one object, and the relationships will also be objects.

 

The normal approach looks something like:

$user = new User();
$user->id = 1;
$user->username = "Thomas";
$user->email = "email@email.com";

 

 

The objects will then have methods for adding, usually called save(), finding with find() and etc. The point is of course that this approach will limit the amount of knowledge needed for working with a DB, as it's abstracted into a more common form for many programmers. There's both advantages and disadvantage to this approach, the largest disadvantage often being memory use and speed. The benefits will, at least sometimes, result in shorter development time.

 

These solutions are often really clever things that needs access to a lot of meta data for your tables to work. ORMs are also known for producing sloppy DB design, (normalization, relations, etc) but that is not really the ORMs fault. An ORM should only make DB interactions easier, (as this thread is about) not build your DB design for you.

 

CodeIgniter has a different approach to this, and while it does not really feel wonky or bad, I just don't like it. My biggest problem is the same as with several other CodeIgniter (CI) libraries/helpers. They offer to many weird methods, and the methods often have bad design in my opinion. They offer several methods for SELECT, FROM, JOIN, WHERE, GROUP BY and ORDER BY, but seem more interested in short syntax than clever methods. In the end, you end up with weird method SQL, that won't work without second or third parameters with false, or null. It's not hard to understand or difficult to use, I just don't see the gain.

 

How is this...:

 // Select part of query
$this->db->select('brand.brand_id AS make_id');
$this->db->select('model.model_id AS model_id');
$this->db->select('brand.brand_name AS make_name');
$this->db->select("CONCAT(brand.brand_name,' ',model.model_name) AS model_name", false);

// Join data
$this->db->from('eddy_cars_brands AS brand');
$this->db->join('eddy_cars_models AS model','brand.brand_id = model.model_brand_id', 'left outer');
$this->db->order_by('brand_name');
$this->db->order_by('make_name');

 

Really better than this...:

SELECT
brand.brand_id AS make_id,
model.model_id AS model_id,
brand.brand_name AS make_name, CONCAT(brand.brand_name,' ',model.model_name) AS model_name
FROM cars_brands AS brand
LEFT OUTER JOIN cars_models AS model ON (brand.brand_id = model.model_brand_id)
ORDER BY brand_name, make_name

 

One of the benefits of Active Records is being able to do something like this:

 

// Add a User
public function add( User $u ) { $this->db->insert('table', $$u); }
// Update a User
public function modify( User $u ) { $this->db->insert('table', $$u); }
// Delete a User
public function remove ( User $u ) { $this->db->where('user_id', $u->get_user_id())->delete('table'); }

 

Active records is simple supposed to easy your work, but you need to expect some drawbacks too. I don't really think it's a good solution unless you know what you're doing.

  • Upvote 1
Link to comment
Share on other sites

Sounds like active records (writing them, at least) are only beneficial if you're writing a framework for other (less experienced) people to use.

Anything short of that sounds like a waste of time and very inefficient.

 

Still, thank you very much for explaining all that. Very interesting.

And to be honest, I had never thought about abstracting the DB interface layer that far, but then again, the native state of a DB for me is not some OOP or JSON format, but the standard SQL format.

 

Anyway, thanks again.

Link to comment
Share on other sites

Hey AC, Ive got an idea why don't you make a more efficient framework for us? I think you will find Yii is slightly much better than codeignitor. I don't even understand why would you use codeignitor when it is less superior especially considering the fact that learning a framework would take time and effort.

 

Some good sites have been built with frameworks I disagree that people less experienced use them. I built my own Object orientated mvc and it worked fine for me. But in my opinion it would be smarter to use a framework for developing a fairly large site as it has been tested in more conditions by more users therefore bugs will be fixed and site will be more stable. And with regards to code ignitors query builder statements those are fine just think of what is the end result a string is concatenated and pasted to SQL. I bet that would be faster than the time a query would take on a large number of with a records.

Link to comment
Share on other sites

I typed last message on my iPad message is between yours AC, sorry about that. Disregard this fixed the above statement, by the way no hard feeling. Just saying sometimes it's better we get on and use what we have rather than bitching about it.

Link to comment
Share on other sites

While I don't want to put words into Antonio's mouth, he seems to more or less agree with me, and my opinion is that frameworks, when possible, should be avoided.

I don't think it's a matter of thinking their "bad" or that I could build a better one, but given their nature, there are inherit inefficiencies that, when you write your own functions/methods for individual projects, can be avoided.

As is the case with anything, if you have the skill and expertise to customize something instead of using a generic, out-of-the-box solution, you will always end up with a better result.

Link to comment
Share on other sites

Hehe. No problem. The only reason for using CI is that I've used it before. I didn't want to learn a new one for this project. I can tell your right now that it doesn't take much to be better than CodeIgniter. I knew CI. It was a safe bet. Choice made.

 

Also, Edward. I did not ask how to perform a task. I can read documentation perfectly fine. I've never used YII before, though, and I know several users here has done that. As this thread is related to approaching the DBMS, I asked what they thought about YII's active records or query builder. I want to know their EXPERIENCE using these tools. I did not ask how it works. You really need to read what I write properly, my friend. ;)

Link to comment
Share on other sites

Hehe. No problem. The only reason for using CI is that I've used it before. I didn't want to learn a new one for this project. I can tell your right now that it doesn't take much to be better than CodeIgniter. I knew CI. It was a safe bet. Choice made.

 

Also, Edward. I did not ask how to perform a task. I can read documentation perfectly fine. I've never used YII before, though, and I know several users here has done that. As this thread is related to approaching the DBMS, I asked what they thought about YII's active records or query builder. I want to know their EXPERIENCE using these tools. I did not ask how it works. You really need to read what I write properly, my friend. ;)

 

Seriously these threads you are making are completely pointless a waste of time there is really nothing to talk about. If you actually even had a website and could even put to the test what you were talking about. It would be at least be more interesting if we a certain number of records to test our queries on and at least get a time for completion back. If our query was taking 10 seconds then yeah we should probably have a talk on here about what was better but you guys have nothing so what are you talking about?

Link to comment
Share on other sites

While I don't want to put words into Antonio's mouth, he seems to more or less agree with me, and my opinion is that frameworks, when possible, should be avoided.

I don't think it's a matter of thinking their "bad" or that I could build a better one, but given their nature, there are inherit inefficiencies that, when you write your own functions/methods for individual projects, can be avoided.

As is the case with anything, if you have the skill and expertise to customize something instead of using a generic, out-of-the-box solution, you will always end up with a better result.

 

I have a friend also who is a coder that knows more than any of us here and even he uses a framework working for a larger company. They use the framework to save development time and reinventing the wheel because "Time is Money" The framework can also be customized and useful plugins can be installed. There are other useful frameworks like CSS grid frameworks, jquery etc are you not going to use those and just make all those yourself? Seems you are more interested in coding than actually get your ideas to work. I will be honest i don't even care for PHP, SQL they are not my languages but as far as i am concerned they get the job done. Also with regards to Yii, i wouldn't say that's easy to learn that's full blown OOP code i heard a friend of mine saying he was struggling with the Yii code, it takes time to learn.

 

Anyway guys, here is a link to the Yii Performance page, its worth a read:

 

http://www.yiiframew...om/performance/

Link to comment
Share on other sites

 Share

×
×
  • Create New...