• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

NULL fields in database

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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]
reply
    Bookmark Topic Watch Topic
  • New Topic