Using Null field to represent something?

Question

Is it ok to use 'NULL' value in the deal.tariff_id to represent that it belong to any tariffs in the tariff_data table with affiliate_id=3?

For example:

mysql> select * from phone;
+----+------------------+
| id | name             |
+----+------------------+
|  4 | HTC Sensation XL |
| 26 | iPhone 4s        |
| 25 | iPhone 5         |
| 24 | Nokia C3-01      |
+----+------------------+

mysql> select * from tariff;
+----+-----------------+-----------------+--------------+-------------+
| id | name            | tariff_duration | monthly_cost | description |
+----+-----------------+-----------------+--------------+-------------+
|  1 | Business Plan 1 |              24 |         5.00 |             |
|  2 | Business Plan 2 |              24 |        10.00 |             |
|  4 | Business Plan 3 |              24 |        15.52 |             |
|  5 | Business Plan 4 |              24 |        18.52 |             |
|  8 | Super Plan      |              12 |        15.00 |             |
+----+-----------------+-----------------+--------------+-------------+

mysql> select * from tariff_data;
+----+-----------+--------------+-------+
| id | tariff_id | affiliate_id | bonus |
+----+-----------+--------------+-------+
|  1 |         1 |            3 | 34.00 |
|  2 |         2 |            3 | 44.00 |
|  5 |         3 |            3 | 10.00 |
|  6 |         4 |            3 | 10.00 |
|  7 |         5 |            3 | 10.00 |
+----+-----------+--------------+-------+

On a deal table, you can see affiliate_id=3 and tariff_id=NULL is belong to any tariff that are listed in tariff_data table. I did this to reduce amounts of rows in the deal. If I don't include NULL that mean I have to include many tariff_id with same phone_id and vice versa.

mysql> select * from deal;
+----+----------+-----------+--------------+--------+
| id | phone_id | tariff_id | affiliate_id | active |
+----+----------+-----------+--------------+--------+
|  1 |        4 |      NULL |            3 |      1 |
|  3 |       24 |      NULL |            3 |      1 |
|  9 |       24 |         8 |            4 |      1 |
| 10 |       25 |         8 |            4 |      1 |
| 11 |       26 |         8 |            4 |      1 |
+----+----------+-----------+--------------+--------+

Update, example if I did not use NULL value:

mysql> select * from deal;
+----+----------+-----------+--------------+--------+
| id | phone_id | tariff_id | affiliate_id | active |
+----+----------+-----------+--------------+--------+
|  1 |        4 |         1 |            3 |      1 |
|  2 |        4 |         2 |            3 |      1 |
|  3 |       24 |         1 |            3 |      1 |
|  4 |       24 |         2 |            3 |      1 |
|  5 |       24 |         3 |            3 |      1 |
|  6 |       24 |         4 |            3 |      1 |
|  7 |       24 |         5 |            3 |      1 |
|  9 |       24 |         8 |            4 |      1 |
| 10 |       25 |         8 |            4 |      1 |
| 11 |       26 |         8 |            4 |      1 |
+----+----------+-----------+--------------+--------+

Answer 1

Don't use NULL to communicate a message. Use NULL to indicate the absence of a value.

The absence of a value can mean that a value exists, but is unknown. This case was covered by Oded.

It can also mean that a value is inapplicable in this case. This often happens when a table contains rows that describe objects of a given class, but there are multiple subclasses that extend the class. Some columns might pertain to some subclasses but not others.

You can get around this last case if you need to, by a technique that has a tag named "Class Table Inheritance" in SO.

If readers of the data draw inferences from the absence of a value, they can. But inference is not communication.

Answer 2

Not a good idea. The basic problem is that NULL already means three distinct things and this makes them problematic. You are asking problems. What it represents are:

1. Unknown value (i.e. not entered, expected to exist). This means that NULL || 'mytext' IS NULL.
2. Value known not to exist (say, using NULL in the second line of a street address). This is problematic but very difficult to avoid. On non-Oracle platforms, empty strings are better here for character types. It looks like you are weighing this one.
3. Value not found, in the results of an outer join

What this means is that you already have three distinct cases you have to be prepared for when addressing a NULL value and you have to use context to figure it out. If you can reduce the meanings to two, or better yet 1 (outer join no record found) then you are better off and you have fewer cases to worry about.

The problem of course has to do when one is expected to have one of two values present, for example employee.hourly_wage or employee.monthy_salary. This is of course a bad example since one could do wage_or_salary plus a pay_unit, but the problem is pretty universal. Very often you have to trade between perfect normalization and avoiding nulls in columns on one hand and rich cross-column data constraints on the other.

Answer 3

As the other two answers, this is not a good idea. Here's a suggestion for normalization.

You want some of the deals to represent a "general" deal, irregardless of tariff. You can split the deail table into two, one for simple deals and one for the general ones:

mysql> select * from deal_simple;
+----+----------+-----------+--------------+--------+
| id | phone_id | tariff_id | affiliate_id | active |
+----+----------+-----------+--------------+--------+
|    |       24 |         8 |            4 |      1 |
|    |       25 |         8 |            4 |      1 |
|    |       26 |         8 |            4 |      1 |
+----+----------+-----------+--------------+--------+

mysql> select * from deal_general_tariff;
+----+----------+--------------+--------+
| id | phone_id | affiliate_id | active |
+----+----------+--------------+--------+
|    |        4 |            3 |      1 |
|    |       24 |            3 |      1 |
+----+----------+--------------+--------+

You could then have the expanded results with the simple query (I assume you did not include by mistake 3 more rows that relate phone_id 4 with tariff_id 3, 4, 5):

SELECT phone_id, tariff_id, affiliate_id, active
FROM deal_simple

UNION

SELECT dgt.phone_id, td.tariff_id, dgt.affiliate_id, dgt.active
FROM 
    deal_general_tariff AS dgt
  JOIN 
    tariff_data AS td
      ON td.affiliate_id = dgt.affiliate_id ;

Answer 4

There are a number of ways you could achieve the desired results, but using something more logical than NULL.

As Oded pointed out, NULL is meant to represent a missing unknown value

• There could be a column to indicate ownership
• Normalization
• Use existing column "tariff_id" as the indicator of ownership with a NON NULL value

Answer 5

Not a good idea.

NULL means an unknown value. This is not the case, and using it to have a specific meaning will come back to bite you.

First of all, the convention will have to be known and remembered by anyone touching that part of the system (code and SQL).

Secondly, it is not extensible - what if you need something to represent all tarrifs with affiliate_id of 4?

Third - what do you do if you actually need to use NULL?