SQL connectivity best practices.

dsf

So I've got this data model I've been working on and I'm about done with it. So I'm at the point where I have to generate SQL queries to check the tables associated with it to see if any of the data in my model is flagged as deleted in the database. If it is, then I won't be generating an SQL query for that particular piece. So the question is, should I jack hammer the data base with intermittent queries(safe but a lot of traffic), or try to do them all at once(if it crashed for whatever reason could I lose data)? What would be the best practice here?

Byron

So, I don't know exactly what you are doing, but I don't understand why "try to do them all at once" would cause you to lose data.

Generally if you want to perform a large scale operation (or set of operations) that is fully reversible, you would wrap it up in a transaction (BEGIN...END). I'd say try to do them all at once so that it is atomic. Wrap up all the ops in a transaction. Test the transaction for failure; if it did fail (crash for whatever reason), then simply redo the entire transaction over again in your client software. Still better than thousands of little hits.

Perhaps I misunderstood something.

dsf

So, I don't know exactly what you are doing, but I don't understand why "try to do them all at once" would cause you to lose data.

Generally if you want to perform a large scale operation (or set of operations) that is fully reversible, you would wrap it up in a transaction (BEGIN...END). I'd say try to do them all at once so that it is atomic. Wrap up all the ops in a transaction. Test the transaction for failure; if it did fail (crash for whatever reason), then simply redo the entire transaction over again in your client software. Still better than thousands of little hits.

Perhaps I misunderstood something.

My mentor has a degree in Graphic design and I'm an intern about to graduate with a programming degree. Professors teach you the language but not the industry practices and this mentor really just does the bare minimum to get his site working for his client without thought to the programmers down the line who may be using this code. I don't want to be an ok programmer I want to be an exceptional one, so I feel I need to reach beyond this experience to find out the things my mentor is too l33t to know/do.

Byron

Ah, see the funny thing is when you posed the question, I sort of wondered if "ITS A TRAP!" and you were going to tell me one hundred reasons why my suggestion was not, in fact, the optimal approach.

I've come to assume and expect 1337, apparently. It's obnoxious.

I did get a degree in computer science. Although it had a focus on IT, we were definitely taught how things work at a low level to understand how to make wise decisions in various implementations at higher levels. My personal education included almost no "best practices", although the real world tends to focus on that more.

Something you might wonder is whether you should shoot for "best practices" in industry or "best practices" in theory, as I find they sometimes aren't the same thing. Someone else might be better suited to address my comments though. I've only worked in academic and government sectors, never private.

Byron

As a secondary thought, if you are ever in a position to design or redesign the database schema (the table layouts with their columns, and indexes and all that stuff), you should definitely do some research into normal forms.

Key take away for database design: reduce redundancy.

Quick and poorly defined example:
if you have a database storing places people live, you'd want to break that down into people (each person is distinct), places (each place is distinct), and some way of relating which places which people live in. In this case, multiple people can live in one house, one person can live in multiple houses, and multiple people can live in multiple houses. The optimal way to reduce redundancy in these situations is to create one table with people (giving each person a unique number), one table with places (giving each place a unique number), and one table that has two columns: a person and a place.

If you ever need to change the details of a place, you only need to update it in one row of one table in the database. Same for a person. The usual solutions to database design have redundancy so that the same place might be listed twice if two people share a residence, which can get ugly if a residential phone number or zip code changes and not all the entries are updated properly.

Soooooo this was a big long post that might have been totally worthless. I hope you enjoyed it

rubner

SQL connectivity can be crucial if you are going to establish a system that would be dependable on such a database. Always remember than with querying, it is best to subject queries in prepared statements as directly checking if keywords match, can be a loophole which can be exploited by hackers or those who want to get into your system.