CODEWITHSUNDEEP
sqlsql-server
GibboK
GibboK

Reputation: 73908

Shall I set a column on NULL for optional values?

I'm using a SQL Server database, I'm working on a real estate website.

I have a table listing the properties with a column called SIZE.

A user can optionally input a SIZE value for a property (only integral and positive numbers).

At the moment I'm using this

int   Size   NULL

I would like to know:

Please if you consider this question inappropriate just comment, I would remove it from the site.

Thanks

Upvotes: 1

Views: 259

Answers (3)

Bohemian
Bohemian

Reputation: 424973

NULL is the "correct" approach, but it can make writing queries a pain, because NULL won't match any predicate except IS NULL. For example, say you want a query that finds all rows that have a size between x and y, but you also want to show rows that don't have any size. Your query will look like this:

select * from some_table
where size between x and y or size is null;

This can introduce unnecessary complexity, and you also need to deal with how to render a null size.

A better option IMHO is to make the column NOT NULL and force the user to input a size. That will simplify both your application and query code.

Don't tell me that customers own a property and don't know how big it is.

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

If NULL value in this case would make sense (I read some article that discourage the use of NULL so I would like if this is the case)

NULL is fine if you want something that represents a non-value. It's better than making up a different representation (e.g. -1 in numerics or an empty string) for each different datatype. We can't really comment further without seeing the article you read and knowing what argument it was putting forward (and in what context).

The value of size should be from 0 to 100000, I would like to know if the DataType int is appropriated.

It's the most appropriately sized integral type - tinyint and smallint have lower maximum values than 100000. If you want to be thorough, add a CHECK constraint to your table also:

CREATE TABLE Properties (
    -- Various Columns
    Size int null,
    constraint CK_Properties_Sizes CHECK (Size between 0 and 100000)
    -- More columns, constraints, etc
)

Upvotes: 2

Burhan Khalid
Burhan Khalid

Reputation: 174614

NULL here is correct, as it specifically means no value was entered, which is what you are trying to represent. Any other value would actually have some meaning. For example 0 is a valid value - even though it may not make sense in this context. Specifically setting it to NULL means that you are explicitly stating that no value was entered.

As for max int question, from the documentation:

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Upvotes: 2

Related Questions