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 |
+----+----------+-----------+--------------+--------+

score 2 · Answer 1 · answered Oct 06 '12 at 20:20

2

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

answered Oct 06 '12 at 20:20

krg

2,650
2
12
9

Please show example of "column to indicate ownership" you meant and how would you Normalize – I'll-Be-Back Oct 06 '12 at 20:29
It seems that you are using the tarrif_id column as this indicator no? What does the non NULL values currently represent? As far as normalizing, I am not strongly suggesting this -- I was just listing out common solutions. Your tables already look normalized. – krg Oct 06 '12 at 20:31
If I use `tarrif_id` column as `NULL` it mean it indicate that `phone_id` belong to any `tariff_data` table (`deal.affiliate_id = tariff_data.affiliate_id`). The None NULL values currently represent get related records between `deal.tariff_id = tariff.id` – I'll-Be-Back Oct 06 '12 at 20:52
Fair enough - This may become philosophical but what I would do (personally) is use a NON NULL int value to represent 'any' ownership. MySQL defines [NULL](http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html) as "a missing unknown value." This use is actually "a known value" of 'any' ownership. In the same vein, you could conceivably use null to represent 'any,' it just seems limiting and possibly confusing. – krg Oct 06 '12 at 21:05
I understand what you mean. I could define all the `tariff_id` for each `phone_id` in the `deal` table but it get really big if you know what I mean? I have updated my question so you can see example what I meant. – I'll-Be-Back Oct 06 '12 at 21:20
I wouldn't go that route. Have a look at this [thread](http://stackoverflow.com/questions/167952/null-or-empty-string-to-represent-no-data-in-table-column) as it seems like a similar question. – krg Oct 06 '12 at 21:41

score 1 · Answer 2 · answered Oct 06 '12 at 20:00

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?

score 1 · Accepted Answer · answered Oct 06 '12 at 22:47

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 ;

This is fantastic idea, thank you very much! Just a quick question. There will be some tariffs that are "Sim Only Deal" (in `tariff` table) which mean it does not come with the phone. Would you put this in `deal_simple` or `deal_general_tariff` with `phone_id = NULL` ? — I'll-Be-Back, Oct 07 '12 at 00:51
You mean deals that are independent of phones? Then you could have a `deal_general_phone` that would have all columns as the `deal`, except the `phone_id` (same idea as above) — ypercubeᵀᴹ, Oct 07 '12 at 00:55
No. For example customer want to order a Sim-Only deal (tariff) on contract but they already own a phone.. They want to order Sim-Only contract (tariff) without a phone. In that sense. maybe like `deal_without_phone` or `deal_simonly` — I'll-Be-Back, Oct 07 '12 at 00:59

score 1 · Answer 4 · answered Oct 07 '12 at 05:20

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:

Unknown value (i.e. not entered, expected to exist). This means that NULL || 'mytext' IS NULL.
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.
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.

score 0 · Answer 5 · answered Oct 07 '12 at 12:35

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.

Using Null field to represent something?

5 Answers5