NULL fields in database

[Migrated From Jforum.net]

currently mysql often uses NULL value in database for indetification of yes/no.
In many databases (most notably mysql) if you declare a field as NOT NULL and use 0/1 values instead for yes/now the database often work significantly faster than with NULL.
Good thing = for 0/1 values you probably do not need to update queries, just change the fields declaration.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Good database practice says that null is not yes or no, it is simply null, so it should not be used as a value but as "absence of value".

If it is mandatory to have a value, then you specify "not null", but one might need the possibility to be able to set it to null, as a way of saying "not decided, yet". So, IMHO, setting it always to "not null" is not a good rule of a thumb.

I don't understand the part about not needing to use an update query to change the value when using 0/1 as flag. How would you do that? I have no experience with mySQL so I'm curious to learn new ways of doing things .


[originally posted on jforum.net by GreenEyed]

[Migrated From Jforum.net]

GreenEyed wrote:Good database practice says that null is not yes or no, it is simply null, so it should not be used as a value but as "absence of value".

If it is mandatory to have a value, then you specify "not null", but one might need the possibility to be able to set it to null, as a way of saying "not decided, yet". So, IMHO, setting it always to "not null" is not a good rule of a thumb.

I don't understand the part about not needing to use an update query to change the value when using 0/1 as flag. How would you do that? I have no experience with mySQL so I'm curious to learn new ways of doing things .

I read somewhere on mysql.com that if a field is decalared as 'int not null' that the database would work faster than when it just 'int' with some of the values in table =NULL.

my previous statement was actually correct only regardsing jforum, where
initial value is NULL and write to the database makes it 0/1
for example src/net/jforum/dao/generic/GenericUserDAO.java
public void setActive(int userId, boolean active) throws Exception
{
PreparedStatement p = JForum.getConnection().prepareStatement(SystemGlobals.getSql("UserModel.activeStatus"));
p.setInt(1, active ? 1 : 0);
p.setInt(2, userId);

p.executeUpdate();
p.close();
}
so a query
SELECT * from jfroum_user WHERE user_active;
would work ok if a field is declaration would be changed to INT NOT NULL (the defauld will be 0).

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Well, I did not say what you were saying is not correct. In fact, I said you are correct in that using "null" to mean "not active" should not be used. However, what I say is that "null" has its own value, which is "undefined value" so sometimes it is necessary to allow some columns to be nullable.

In this example, if you don't allow the active column to be nullable, you cannot create a user record without deciding at creation moment whether it is active or not. Sometimes that is acceptable, sometimes it is not.

I just wanted to say that setting the column to not nullable has more side effects than just improving speed, so it must be carefully studied.

Besides, JForum is software designed to be protable across databases, so modifying the database structure because of an implementation detail of a specific database is something that also needs to be studied very carefully, as it affects the whole app and not just when it runs on MySQL.

So just saying that it is not that easy as saying, "it works like that in mySQL so let's change all of them to work like that" More things have to be considered.

Cheers
[originally posted on jforum.net by GreenEyed]