I know that there have been many opinions/sides concerning NULL values in a database.
I have not understood though what is the best practice for this.

I.e. if I have a relational table with an optional attribute i.e. it can take NULL value -so we can end up with a table with many NULLs on that column- is it best to make the attribute a new relational table?

What is the best approach on this?

  • 5
    Best from what perspective: performance? ease of use? relational theory? – APC Jan 4 '12 at 7:57
  • 1
    What's the reason why you would consider this ? – Frederik Gheysels Jan 4 '12 at 7:58
  • @ APC:From relational theory.From performance it would make sense I guess to leave the nulls in the table – user384706 Jan 4 '12 at 8:00
  • @ Frederik: It is more for a better understanding of how it would be done strictly in relational theory.I hope this makes sense to you – user384706 Jan 4 '12 at 8:01

From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null.

As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are therefore likely to have a large number of NULL values in the end date column, yet it would be a bad choice to move this out into a separate table.

However, as an implementation issue, there may be good reasons to split out certain fields into separate tables. For example, consider a customer address table; certain addresses may require complicated delivery instructions, yet the vast majority are not likely to require them. In such circumstances, it would make sense to have a separate table for address delivery instructions, even if it has the same key (customer address ID) as the main customer address table.

(As a side note, some experts on relational theory - such as Chris Date and Fabian Pascal - are opposed to allowing NULLs within relational databases at all.)

| improve this answer | |
  • +1. Also clarified my answers columns vs values after reading this... – gbn Jan 4 '12 at 9:36
  • 2
    it would be a bad choice to move this out into a separate table - please explain... What stops you having a separate table AccountClosure (AccountID, EndDate) ? – finnw Aug 17 '12 at 23:43

If your perspective is "from relational theory", as you claim in that comment, then the answer is simple : null is not a value, and a thing that contains a null is not a relation, and a database that can contain a null is not a relational database. From the perspective of relational theory, there just is no "best" choice, because there is no choice at all.

(Note that this does not mean that at the physical (implementation) level, there could not be constructs such as null. But these constructs should not be exposed to the database user, because the DBMS is precisely supposed to insulate its users from implementation stuff, leaving him to interact with the DBMS exclusively at the logical level, which cannot involve nulls.)

| improve this answer | |

A relational table in BCNF with many NULL columns values (optional attributes) either isn't normalised or is some anti-pattern. It is that simple usually.

Saying that, in some cases is may be an acceptable implementation decision. For example, subtypes in the logical model collapsed into one supertype table at implementation time.

Whether to have any NULL values is a different issue and concerns higher levels of normalisation. See dba.se for 6NF questions

Edit, for many NULL values in a single column, see Mark Bannister's answer

| improve this answer | |
  • 1
    Do you view the absence of NULL values as being a direct consequence of BCNF, or is it simply that NULL values are likely to have been normalised out by the time a table reaches BCNF? As far as I can see, BCNF itself relates to the validity of candidate keys/superkeys. – Mark Bannister Jan 4 '12 at 9:08
  • 1
    On the other hand, ensuring no NULL values sounds like EAV ;-) – pst Jan 4 '12 at 9:12
  • @MarkBannister: neither. I said "A relational table in BCNF with many NULL values". I didn't say no NULL values. That's 6NF IIRC but if you have a table with 100 nullable columns, would you not question why this is so? – gbn Jan 4 '12 at 9:15
  • @pst: EAV can have NULLs too. Check my link to dba.se about 6NF... – gbn Jan 4 '12 at 9:22
  • 2
    Strictly speaking there is no such thing as "a relational table in BCNF with many NULL columns." NULLs violate 1NF, and 1NF is a prerequisite for all higher normal forms including BCNF and 6NF. – finnw Aug 17 '12 at 23:40

I don't see any 'relation theory' why you would make a table to store an attribute, just because that attribute can be null.

| improve this answer | |
  • I thought that if the column could have a large number of NULLs it could be considered more efficient to be stored in a separate table. – user384706 Jan 4 '12 at 8:09
  • @user384706 Don't worry about "efficiency" like this. There are 30+ years of SQL implementations that are "going to get it correct" more times then you are :) [If you have a limitation, you will know it. You will also know how to profile it, and then react.] – pst Jan 4 '12 at 9:07

First, there is no such thing as a best practice. Next, the question is not clear about whether you have a blank slate for relational design or not, and whether you are targeting a particular RDBMS or not. As for null values, that is a religious war which side of you are on you can most succinctly answer by reflecting on what your implementation choice is if you want to have an option to store someone's middle initial (and you know that some folks don't have a middle initial). Now moving on to a particular implementation case, perhaps involving an entity with more than 254 columns and using an Oracle database, it is likely that overall performance will be improved if the columns are ordered by likelihood of having a non-null value, breaking ties by perhaps the columns of your primary key in order and then it is largely up to you although you might put index component columns leftish just because it is easier to grok when considering a narrow bit of a wide listing. Following on, if many uses of the table don't inquire on the value of the likely to be null columns and the ones beyond column 254 end up in another table with a one-for-one key, you can usually devise a physical structure and access plan that ends up being easy to use, sensible and fast.

Please notice that I did not select a religious choice for you in the question about handling the middle initial and that I did NOT attempt to address the general case of having a blank slate designing a model with columns unlikely to need nulls.

If you have a wide existing table containing lots of frequently null columns, it is likely you can make it more efficient if you re-arrange the physical table so that most of the nulls are contiguous to the right hand side of the table. If the table is over 254 columns in certain RDBMSes, you may profit from breaking the table into two pieces such that the overflow is less often referenced and when you need the whole it is via a view sliciing the two physical tables together one-for-one.

| improve this answer | |

What do you mean with 'a new relational table'?

If your column is set to be UNIQUE, the content must be unique, not the same as any other row. This is when NULL or NOT NULL comes in to play. Giving the option to be UNIQUE or NULL / empty.

| improve this answer | |
  • 1
    Unfortunately this is database-dependent and is "vague" in ANSI SQL. SQLite considers all NULL values unique from NULL values. SQL Server will not allow multiple NULL values in a UNIQUE column, however, which is a real PITA to "hack about". – pst Jan 4 '12 at 9:09
  • 1
    @pst: This is not vague in ANSI SQL. SQL-Server does not implement the standard regarding NULLs and Unique constraints. – ypercubeᵀᴹ Jan 4 '12 at 10:07
  • 1
    @ypercube, Unfortunately it is vague. If a column is both nullable and UNIQUE, some DBMSes interpret this as "only one row may have a NULL in this column", and others as "non-null values must be unique, but unlimited rows may have NULL in this column." If you want your schema to be portable between SQL DBMSes (good luck!) then declaring all columns NOT NULL will help (a little.) – finnw Aug 17 '12 at 23:49
  • 1
    @finnw: What is vague? The ANSI/ISO standard? Or the implementations? There is no argue that some DBMSes have implemented it as you say "only one row may have a NULL in this column" (where "some" means "SQL-Server"). – ypercubeᵀᴹ Aug 18 '12 at 9:57
  • 2
    It is not "vague" in standard SQL. UNIQUE is a constraint, constraints are violated only if their defining boolean expression evaluates to FALSE, if an involved attribute is NULL, then that expression will evaluate to UNKNOWN, which is not FALSE, hence constraint not violated in such a case. – Erwin Smout Oct 4 '12 at 22:54

IMO the decision to make your attribute a new table has more to do with normalisation design than NULLs.

That said, if you do decide to normalise the attribute, then you have a couple of options

  1. Leave the foreign key field on the first table nullable. You would need to OUTER JOIN to the attribute table to prevent null attributes being filtered by the join.
  2. Insert a 'dummy' record into the attribute table, e.g. a record representing an 'unspecified' value for the attribute and then making the foreign key attribute non nullable, possibly also defaulting it to the dummy value.

1 is a more traditional and natural representation IMO, however 2 has the benefit that inner joins are guaranteed, and also can ease code which uses the DB as you no longer need to check for nulls (although undoubtedly you will need business logic to check for your 'dummy value' in places).

| improve this answer | |
  • I was more thinking to have a new table with primary key, the primary key of the first table that has the attribute – user384706 Jan 4 '12 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.