435 reputation
36
bio website
location
age
visits member for 3 years, 8 months
seen Oct 29 at 23:18

May
9
awarded  Yearling
Jan
29
awarded  Good Answer
Aug
13
comment SQL: empty string vs NULL value
Third, databases are designed to store facts and as such it should be designed around the business process and not around the system du jour used to write to it. The db should be designed such that tomorrow whatever system currently writes to the db can be scraped and a new system can be built with few changes to the database itself. I am very well versed in OOP and relational design and thus I also know that the mindset for database design is fundamentally different than application/system design.
Aug
13
comment SQL: empty string vs NULL value
Second, the database should not trust the layer above it. Some business logic in the form of data integrity rules absolutely should be implemented in the database or else you leave yourself open to the rogue DBA filling your database full of crap and breaking the application(s) that interact with it.
Aug
13
comment SQL: empty string vs NULL value
First, even systems without presentation tiers have to handle absent values if the store from which the data is coming allows for them. Thus, the type of interface, whether user, service, or external system isn't relevant. Regardless of which type of system reads and writes to the store, if the store fundamentally provides for the concept of an absent value, then the calling system must also account for them.
Aug
12
comment SQL: empty string vs NULL value
Even business logic has to account for (meaning represent) absent values and that is true in my experience, in almost every system I've seen or built in the past 20 years. The database is modeling the business facts to be captured and stored. If the business logic wants to be able to interact with the database it must know how to deal with nulls. Whether it is a custom struct, magic value or a generic is irrelevant. The business logic needs the ability to handle the receipt of an absent value from the database and the ability to mark a value as absent to the database.
Aug
10
comment SQL: empty string vs NULL value
You "never" have to deal with nulls. So, what you describe is ostrich solution combined with the the magic value solution. "I'll ignore the fact that absent values exist and I'll convert all null integers to -1". Until the day comes when -1 is a real value. It should be noted that one of the reasons MS added generics to .NET was to address the massive impedance mismatch between databases and applications code and that primarily revolved around expressing nulls in middle-tier code. Those "silly nulls" exist in business logic too.
Aug
10
comment SQL: empty string vs NULL value
Examples where the database is not used solely by the original application: reports, integrations with other systems.
Aug
9
comment SQL: empty string vs NULL value
Databases don't live without applications that use them. In my experience, this is simply not true and short-sighted. Almost always the database gets used outside of the application for which it was designed. In general, databases survive longer than the applications for which they were built. Databases should be designed to collect facts about the business and the UI should be built to read and write to the database not the other way around. Relational design is an entirely different mindset than application design.
Aug
8
awarded  Critic
Aug
8
comment SQL: empty string vs NULL value
-1. Whether the database is driving a website or not is irrelevant. Designing databases is different world than web design. The database should be designed to capture facts about the business domain independent of interface used to write to it. By your logic, should you use nulls if coincidentally the first application is an executable? What happens if the first app is a web application but the next application is a mobile app? Design the database to capture facts using normalization rules and design the web site to write to it.
Aug
8
comment SQL: empty string vs NULL value
+1 - This is what I call the "Magic Value Solution". We have to come up with a magic value for each data type to represent an absence of a value. In addition, in some columns the common magic value is or becomes a legitimate value and thus a new magic value is needed.
Aug
8
comment SQL: empty string vs NULL value
No null values. This is the Ostrich approach. "We'll stick our head in the sand and declare that absent values do not exist". That usually leads to the Magic Value Solution where you have to come up with a magic value for each data type to represent an absence of a value.
Aug
8
comment SQL: empty string vs NULL value
Empty cell and null are both way to mark something is wrong. Not true. A null is a way to indicate an absence of a value. I bet most RDBMS use a bit array on each row to indicate which columns are null. Thus, the additional space is so tiny as to be irrelevant. Worrying about the additional processing is premature optimization and will be nothing compared to the speed bumps created for other developers to "discover" that you have intentionally used empty strings.
Aug
8
comment SQL: empty string vs NULL value
why can't the email field not allow nulls and have a zero length string - Simply put: because any developer that knows anything about databases would never expect that empty strings having magic meaning. You are attempting to make your own magic value to represent what fundamentally already exists in every database: a concept to represent an absence of a value. Why reinvent the wheel? Also, the idea of NULLS is far, far, far from old school. Nulls are keystone to understanding relational database design.
Aug
8
comment SQL: empty string vs NULL value
+1 This is the "least astonishment" argument with respect to developers against empty strings. No developer that comes later would ever expect that empty strings would be used to represent "no email address".
Jun
4
comment Can we provide a login functionality in multiple language?
@mouviciel - It violates the least astonishment rule. No user is expecting their username to be case-sensitive. A user entering their name with the wrong case and told that the username does not exist will generate additional support costs.
Jun
4
answered Can we provide a login functionality in multiple language?
Jun
3
comment Why do some programmers think there is a contrast between theory and practice?
@MauricioScheffer - In my experience, it is unfortunately far more common to see perceived, present correctness favored over readability and maintainability. "Correctness" is not immutable state. What is correct for the solution now may change in the future. In fact, it should be expected to change. If that ingenious, "clever" solution cannot be understood by those that need to maintain it so that they can correctly modify when necessary, it can be as much hindrance as the solution itself. The clever solution becomes a black box that no one wants to touch out of fear of breaking it.
Jun
2
comment Why do some programmers think there is a contrast between theory and practice?
I'd give you a +1 except for the crack about readability. Maintainability is 80% of the cost of software so readability is not a small matter. In addition, when that aeronautical or nuclear engineer is making something that will be manufactured having other people understand it is important. The military, government or even large institutions aren't happy with a magic invention that can't be replicated or understood by anyone other than the inventor.