Where is the best place to set restrictions/constraints on data...

In the database or in the application?

I would think that it is better to set most restriction in the backend database, however, that also seems more complex because you have to do more error-handling when the database protests.

Right now I'm wondering if I need to choose "Not Null" in MySQL Workbench for most of my fields in my Order table, or if I should leave it unchecked and just handle it when I write my PHP code.


TomTees

huh? more error-handling? i don't think so

yes, the database is the best place to implement data constraints

this is actually a much more complex issue, best handled separately

one rule of thumb is that every column should be NOT NULL, and if you feel it has to have the ability to be NULL, then you should also consider splitting this column off into a one-to-zero-or-one related table

also please note that this type of thing is loathsome --

Why don't you think so?

My thinking was that if you database is overly rigid, then you have to write extra error-handling to catch all of the errors the database throws plus handling the errors your PHP could throw. (It's like you're doubling the error-handling.)


I'm pretty sure I clicked my "Easy" button before I posted.

Why is it a much more complex issue and what am I missing?


So it seems that you are in the "Nulls should never exist gorilla group"?!

I have heard over the years that Nulls are bad, but I don't see why there is one group of people that feels they should be eliminated. (In real-life the world abounds with Nulls!)

For example, I have a field "Accept Terms". Until a user registers, that field SHOULD BE Null because you have a "value unknown" which by definition is a "null" and it makes perfect sense. (It is important to know whether or not a checkbox/field was initialized or not.)


So what do you think about "Empty" versus "Null"?

And how would you handle fields like below...


I can see pre-filling fields like below...

but the examples above are trickier.



TomTees

experience


well, if you look at it that way, you'd be silly to do it only once if that one time ~isn't~ the database -- who says your application is going to be the only way data gets into your tables? can you positively rule out the possibility of loading your tables from an imported vendor file?

putting data constraints at the database level ensures data integrity no matter how the data comes in

no, i am not

empty is evil, default would be better

Hah!


Fair enough.

Did I forget to insert another quarter??

Aren't you going to answer my other questions??

You left out the most important parts.



TomTees

r937 and I have different opinions, so please consider my observations to be separate from his. He's got good opinions, but I like mine better. As you hopefully remember from our previous encounter, I'll give you things to think about but I won't tell you what to think!

The only common point for all of the ways to get data into and out of your database is your database. You might have one application, or a dozen. You might bulk load data from another source. You (as a developer) might even be inclined to "tinker" with your data interactively. The only common point to enforce rules to keep you data "clean" is the database itself.

The way that SQL handles NULL values can be charitably described as "interesting" most of the time. The problem is that there are at least six different logical states that are all represented by NULL, ranging from "unknown" to "not specified" and in extreme cases "un-knowable" and all of them are handled the same way by SQL. I won't say that you shouldn't allow NULL values in a table, but if you do you really need to document why you made that choice, which of the possible choices NULL represents, and known logical pitfalls that apply to your choice. It is often easier to find a better answer than to use NULL safely/correctly.

If you let the database determine what data meets the "rules", then there is only one place where you need to worry about what the rules are and one place to get them straight. This implies that consistancy is a non-issue, since there is only one place for the rules to be stated and enforced.

I'm not sure that I understand your issue about code complexity, but I'm going to take a stab at what I think that you meant. I may be all wet (which actually sounds like fun right now, but it isn't an option tonight).

If you are looking at "round trip" complexity where the goal is simply to ensure that the data is clean and correct and to inform the user in understandable terms if/why the data isn't acceptable, then there is zero duplication of effort because the front end accepts the data from the user, passes that data to the database, and displays the result... If the data was clean, the user (via the app) can go cheerfully about their business. If the data is dirty, the database will return an error explaining what the problem was and the app can do a lookup/translate so that it can inform the user just how ignorant they really are. This is as simple as life gets for an application!

If you are looking at interactive assistance and data validation (ala Ajax), then you have two distinct problems instead of duplicate effort. Data validation is one problem, and it needs to be handled consistantly or you'll go mad trying to resolve the "oops contingent" that will plague you (timing can be variable, the plague is guaranteed). User assistance and interactive validation is a whole separate problem that needs to be addressed differently depending on the resources available and issues you need to resolve. This is a much larger issue, so before I spout endlessly please confirm whether or not I'm even on the right track.

-PatP

Pat, I welcome a different point of view and someone playing Devil's advocate!!

(BTW, yes, I remember your long posts from before, and all of that good dialogue didn't go to waste - it just got put on the back-burner for a while. Actually, I am ready to get my back-end wrapped up, so I can dive into Object-Oriented Programming and Design Patterns!)


I think we all can ultimately agree on that.


And therein lies my problem with blindly say "No Nulls Allowed!".


And this is where r937 dropped off for the night (??) and didn't finish answering my most important questions...


Okay.


I tend to bite off large chunks when I undertake new projects! Like the time I was 4 and jumped on a Harley-Davidson while learning to ride a "bike"...

Not only do I want to build my first ever e-commerce site from scratch, but I want to do it using OOP and any other "Best Practices" and things that "cool" developers would do.

I think I was scared about defining everything as "Not Null" in MySQL Workbench because later on in development when I remembered that "Accept Terms (Y/N)??" is blank (i.e. Null) before the user clicks on the check-box during registration, then MySQL would throw and error, and as someone who has never written Object-Oriented code, I'd have to not only be sure that my App properly threw errors when things went awry, but I'd also have to be sure I built in all of that complicated Try-Catch-Throw stuff for all the database errors that MySQL would start spouting because I did something impractical like define every field as "Not Null"?!

Lame. I know. But that is a large part of my "phobia"!!


Tom Tees

where exactly in that huge diatribe did you actually say something that represents a "different opinion" from what i said in this thread so far?

i don't mind holding different opinions, but i don't appreciate the underhanded way you dissed me

I think nullable columns are great and should be put to use where needed. Should every column be nullable, no, but I see no issue using them when there is no value, YET. An example, I have a status table, the current state of an object has a null value for end date, the end date for the prior status for that object has yesterday's date. I do not see any easier/simpler way of handling this task.
There are cases where another table is the correct approach. For instance, you would be keeping track of totally differnt columns of information if you were to rent an object rather than selling it outright.
As for handling errors on data constraints, how is it any different than error handling the constraint within your app? If you are applying data constraint within your app, you would have to check the input to see if it is a Y or N. If it was not you would return an error message. If you enforce at the DB level, you would them be told that your value was not accepted for whichever constraint and you would post your same error message.
Dave

Everything... Your opinion is yours, my opinion is mine, that makes them different.
I didn't intend anything of the sort. I do respect you, and wanted to make it clear that I was offering my opinions which were mine and mine alone.

Tom seemed to be waiting for answers that you hadn't provided. I attempted to answer all of the questions that Tom had asked, regardless of whether you'd attempted to address them or not. In fact I specifically didn't read your postings until after I posted to avoid any "cross contamination" between your posts and mine.

My intent was to offer another point of view, not to cast any aspersions on what you'd done up to that point. I'm sorry if you interpreted my comment that way.

-PatP

I guess my fear of *properly* handling Database Errors is that I may not know every possible error that MySQL could throw. (My background is with MS Access where it is much easier - and forgiving - to catch "database" errors!)

My thinking is that if a field should a positive Integer, that is easier to control and handle in the UI than in the database.

I don't know.

That was just my perception.

More so, this concern was set off because I don't see having Nulls in a lot of different types of fields as "evil". So I didn't want to make every field "Not Null" and then open up a world of Database Errors that I wouldn't know how to handle.

Hey, I'm new to MySQL, PHP, OOP, and OOP Error-Handling?!


TomTees

It would be nice if someone could answer these (re-posted) questions below...

(They are the crux of my questions about Nulls.)

Sincerely,



TomTees

i'll give it a shot


in general i deplore "Empty" columns

- email ---> no value until register
- password ---> no value until register

i would instead say no row until register -- make them NOT NULL

- shipped_datetime ---> no value until it ships
- billing_address_2 ---> may not apply
- shipping_telephone_number ---> may not apply
- shipping_instructions ---> may not apply
- credit_card_number ---> doesn't apply if pay with check
- middle initial

i would use NULL for all of these

Or another row within the table, or another table.

Another row example:
the shipped date could be handled in a status table that keeps a history of the order. Once it ships you enter a new row into the table with a type that applies to shipping.

Another table example:
Credit card number: should you really have a table for payments that has a column for every possible type of payment? cash,check,cc, money order, cashiers check, gold bullion, etc... Maybe instead you have a payment table, which lists that your customer paid xxx amount and the type of payment is credit card. Then in your credit card table, you have the card number, expiration date, the security pin from back, and anything else you need. In your check table, you have the routing number, account number, check number, etc... In your gold bullion table you have weight, price of gold per pound when you sell it, whatever. The fact is that all of these payment methods have different info that you need to keep track of so they really should not all be in a single table and nullable.

Billing address 2, wouldn't that be another row in your customer address table with a type that specifies it as #2?

Shipping instructions: could be in its own table as well. Reason being, this would normally be a large varchar and most times would more than likely be null.

Middle initial... yeah make it null.

By "empty" I guess you mean fields that contain the "empty set" usually represented as single quote, single quote ('')?


Okay, you got me there.

Fair enough.


You would use NULL values for those (i.e. not use Not Null)??

Well, if you don't have a problem with Null values for the above examples, then I would agree with you and say that you have a fair and balanced approach!

Glad to see that you aren't as literal as I initially thought!



TomTees