SQL Insert - settle a bet?

azzeretti

So, I am going to try show no bias in the way I ask this so, settle a bet?

You are writing a small piece of code to insert data into a MySQL (or any DB) table. The table is called clients and the fields are (client_id, name, sort_number, req_num)

Which of the following is a better way to proceed?

Method 1:
Within your code, first run this query:

$results = $handle->execute->(SELECT * FROM clients where client_id=? and name=? and sort_num =? and req_num=?)

If ( ! $results ) { $handle->execute->("INSERT into clients values (?,?,?,?) )" }
else {next}

That is, check for existence of data first and only run the insert if there are no identical records in the database

Method 2:
Create a unique compound index on the 4 fields. Then, within your code, run the insert and {next} on error

OwenM

client_id, name, sort_number, req_num -> one of your fields should be a unique constraint, either client_id or re_num look likely. If you think you don't want duplicates then decide what makes a record unique. Compound keys are a bad idea generally but when made up of more than 2 fields I get an itch in my knee, thinking this cannot be good.

ravendude

#2 is almost always wrong. You shouln't rely on keys for what is essentially an application business rule (uniqueness of certain properties). Unique keys are constraints and should be viewed as a kind of safety net ( which is not to say they shouldnt be rigorously applied). most databases also index on unique constraints which is another plus. So, you should do both is my answer.

Boskowski

There is no right or wrong and the recommended way depends on your technologies used and your overall design and implementation.

Me personally I'd prefer to let the db handle stuff that the db is supposed to handle - db integrity. What I mean is why enforce db integrity at app code level when really the db server should handle that?
I'd write a stored procedure that handles the insert etc and determine by return code from the stored procedure whether the insert succeeded and if not why.

But thats just me, again, there is no black & white here. If this is indeed about settling a bet it sounds like a strange bet to me, e.g. "I bet a piece of string is longer than what you say it is."

Boskowski

OwenM wrote: »

client_id, name, sort_number, req_num -> one of your fields should be a unique constraint, either client_id or re_num look likely. If you think you don't want duplicates then decide what makes a record unique. Compound keys are a bad idea generally but when made up of more than 2 fields I get an itch in my knee, thinking this cannot be good.

Really, why would that be? In a star schema it would but in a normalised design not necessarily.

Boskowski

ravendude wrote: »

#2 is almost always wrong. You shouln't rely on keys for what is essentially an application business rule (uniqueness of certain properties). Unique keys are constraints and should be viewed as a kind of safety net ( which is not to say they shouldnt be rigorously applied). most databases also index on unique constraints which is another plus. So, you should do both is my answer.

Now that is just wrong in my book. Apps are often no more than a presentation layer. Apps (or presentation layers) often come and go while the underlying databases stay in place. IMO having db integrity stuff in the app is poor overall design and usually a symptom of poor db design.

seamus

Boskowski wrote: »

Now that is just wrong in my book. Apps are often no more than a presentation layer. Apps (or presentation layers) often come and go while the underlying databases stay in place. IMO having db integrity stuff in the app is poor overall design and usually a symptom of poor db design.

Indeed, in a world where for one database/application you may have a light windows client, a heavy windows client, a web client and API, all written in different languages and by different teams, ensuring the integrity of data can't be trusted to all of these clients (and is clearly a bad idea).

It is always better for an application client to crap out because the DB threw an unexpected error, than it is for a poorly written client to insert bogey data into your DB.

ravendude

Boskowski wrote: »

Now that is just wrong in my book. Apps are often no more than a presentation layer. Apps (or presentation layers) often come and go while the underlying databases stay in place. IMO having db integrity stuff in the app is poor overall design and usually a symptom of poor db design.

Ok. ill concede that it is not black and white. I tend to treat errors that come back from the database as errors, unique checks are a valid alternative flow.

I did say above you still need constraints (I meant in the dbms if that wasnt clear), just because you query up front doesnt mean you dont need to be rigorous with constraints.

Also, I tend to work on codebases where we support multiple db vendors, often with slightly different exceptions/error codes being thrown in this case.

ravendude

seamus wrote: »

Indeed, in a world where for one database/application you may have a light windows client, a heavy windows client, a web client and API, all written in different languages and by different teams, ensuring the integrity of data can't be trusted to all of these clients (and is clearly a bad idea).

It is always better for an application client to crap out because the DB threw an unexpected error, than it is for a poorly written client to insert bogey data into your DB.

That would be a pretty weak architecture anyway for a number of other reasons, eg. no consistent business logic level validation,
eg format validation. Best to have a service layer of course eg. REST/soap

As I said above, IMO the best policy is to do both.

Boskowski

ravendude wrote: »

Also, I tend to work on codebases where we support multiple db vendors, often with slightly different exceptions/error codes being thrown in this case.

I work with an app like that. Its a monster of a third party thing and it supports all sorts of databases. It leaves practically all the db integrity stuff to the single app client which can be the right approach in that sort of scenario. It can be easier to reduce the db stuff to the most common denominator across the different databases supported.

Boskowski

ravendude wrote: »

That would be a pretty weak architecture anyway for a number of other reasons, eg. no consistent business logic level validation,
eg format validation. Best to have a service layer of course eg. REST/soap

As I said above, IMO the best policy is to do both.

Thats why the answer is 'it depends' (piece of string). Not every app requires an enterprise architecture. But either way one should always leave the db do the stuff that its good at. There is rarely a good reason to let the app code do that kind of stuff.

ravendude

Boskowski wrote: »

I work with an app like that. Its a monster of a third party thing and it supports all sorts of databases. It leaves practically all the db integrity stuff to the single app client which can be the right approach in that sort of scenario. It can be easier to reduce the db stuff to the most common denominator across the different databases supported.

We would still put constraints (keys) in the db schemas also, but would have to code for numerous error codes/xcps as you often just get a key failed exception with vendor specific error codes and messages

smcelhinney

I think the most important question has yet to be asked..

How much was the bet for?

azzeretti

Cheers for the replies. Seems the two stances open up the same debate!