What's best way to leave empty database cells?

The convention is to use null to signify a missing value. That's the purpose of null in SQL Noted database researcher C. J.

Date writes frequently about his objections to the handling of null in SQL at a logical level, and he would say any column that may be missing belongs in a separate table, so that the absence of a row corresponds to a missing value I'm not aware of any serious efficiency drawbacks of using null. Efficiency of any features depend on the specific database implementation you use. You haven't said if you use MySQL, Oracle, Microsoft SQL Server, or other MySQL's InnoDB storage engine, for example, doesn't store nulls among the columns of a row, it just stores the non-null columns.

Other databases may do this differently. Likewise nulls in indexes should be handled efficiently, but it varies from product to product.

The convention is to use null to signify a missing value. That's the purpose of null in SQL. Noted database researcher C.J.Date writes frequently about his objections to the handling of null in SQL at a logical level, and he would say any column that may be missing belongs in a separate table, so that the absence of a row corresponds to a missing value.

I'm not aware of any serious efficiency drawbacks of using null. Efficiency of any features depend on the specific database implementation you use. You haven't said if you use MySQL, Oracle, Microsoft SQL Server, or other.

MySQL's InnoDB storage engine, for example, doesn't store nulls among the columns of a row, it just stores the non-null columns. Other databases may do this differently. Likewise nulls in indexes should be handled efficiently, but it varies from product to product.

Using Oracle db. I assumed null was the way to go until I saw this: bennadel. Com/blog/… Didn't know if I was missing the big picture, not having any experience with databases.

– ggkmath Jun 3 at 21:07 Actually, my intent is not to represent a "missing" value. Rather, the intent is to show there's simply no data that exists. Thanks Bill!

– ggkmath Jun 3 at 21:08 The NULL value is simply the absence of data. Ssome people don't have middle names, and database developers will sometimes use an empty string to reflect that fact, distinguishing it from NULL, which really shows nothing at all. With NULL, as with Socrates, all you know is that you don't know.

– Tim Jun 3 at 21:16 Awesome, thanks! – ggkmath Jun 3 at 21:27 Let's say you have a Date of Death column in your dossier. If you're alive, it should contain no date, no data, and the only way to do that with a DATETIME datatype is with NULL.

Now the purist might want to keep THE LIVING and THE DEAD in different tables and require that DateOfDeath be not null. Aggregations on a column that can contain NULLs will usually result in the rows with NULL falling out of the set. But there are helper functions that let you convert NULL to something.

NULL is messy but it cannot always be avoided. – Tim Jun 3 at 21:31.

Normally databases are said to have rows and columns. If the column does not require a value, it holds nothing (aka NULL) until it is updated with a value. That is best practice for most databases, though not all databases have the NULL value--some use an empty string, but they are the exception.

With regard to space utilization -- disk is relative inexpensive these days, so worries about space consumption are no longer as prevalent as they once used to be, except in gargantuan databases, perhaps. You can get better performance out of a database if you use all fixed-size datatypes, but once you start allowing variable sized string (e.g. Varchar, nvarchar) types, that optimization is no longer possible.In brief, don't worry about performance for the time being, at least until you get your feet wet.

If both are NO, then you may consider re-design. Edit your question and post the schema you have now.

Thanks Damir! Yes, the empty cells are supposed to be empty. They're just in the table because some of the other columns are longer than the column with empty cells in it.

They'll never be filled in the future. I'm currently thinking/planning the schema, and this came up. Wondered if leaving the cells there empty had any drawbacks.

– ggkmath Jun 3 at 21:11 @ggkmath, not sure that I quite understand -- a real example would help. – Damir Sudarevic Jun 4 at 12:02.

There are several schools of thought in this. The first is to use null when the data is not known - that's what it's for. The second is to not allow nulls and either separate out all the fields that could be null to relational tables or to create "fake" values to replace null.

For varchar this would usually be the empty string but the problem arises as to what should be the fake value for a date field or or an numeric. Then you have to write code to exclude the fake data just like you have to write code to deal with the nulls. Personally I prefer to use nulls with some judicious moving of data to child tables if the data is truly a different entity (and often these fields turn out to need the one-to-many structure of a parent-child relationship anyway, such as when you may or may not know the phone number of a person, put it in a separate phone table and then you will often discover you needed to store multiple phone numbers anyway).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.