Null columns vs seperate table

tunage

If i have a table and i know that the majority of records for a column are going to be null should this be in a seperate table or wont it really matter.

For example - i have a client table with name, phone, email, fax etc and i know that the majority of entries wont have a fax number should i store this in a seperate table (client_fax)?

I know that probably 95%+ wont have a fax number so how do you way up the pros and cons of having many null entries or making things more complicated by introducing a new table?

I try to normalize my tables to 3NF, but unsure how null columns come into play.

Is there a "rule or thumb" of how many % of fields can be expected to be empty (0%, 20%, 70%, 99%) before a seperate table should be introduced?

What are your thoughts?

Roger_Ramjet

You get little saving in disk space used when it is only 1 column because the new table has an overhead, it also will require a second column with the primary key of the main table and an index for that column. If the column is variable length (varchar, tetx, blob) you will not be using any disk space for the null rows anyway, so an additional table may cause an increase in disk usage.

You will suffer a performance hit in query processing because of the additional table join.

So there is no good case for the additional table, and a good case against it.

thorpe

for tasks like this (user systems) i usually make 2 tables. one for required (username,pass) and one for details.

CREATE TABLE users (
id,
username,
password
);

CREATE TABLE userdetail (
id,
user_id,
key,
value
);

you can then store a username password combo in the users table, and say you want a users email address, you would store it in userdetail like so.

INSERT INTO userdetail (user_id,key,value) VALUES ($userid,'email','bob@bob.com');

this enable you to add as many different types of user details without ever having to add a new field, and also stops nulls.

whenever you want to retrieve a users details you simply query for all records with there user_id.

Roger_Ramjet

An unneccessarily complex solution Thorpe, unless your data is absolutely unpredictable. And how do you cope with different data types?

To just return the details for 1 user, you have to loop through multiple rows to assemble the complete record.
When your query returns multiple users, you end up with loops within loops.
Can't get an easy count of users from the result, have to query seperately or keep a count within a loop.

And, in the end you get no appreciable disk space saving. Columns like 'email' will be variable length, so empty columns will only take up 1 or 2 bytes.

Now, if it were the case that you needed to cater for multiple multi-line addresses for a particular user, then yes, breaking them out into a sub-table is the 'normal' way to go. If you had to catch and store client data-sheets that alwasy came with different field names and varying numbers of fields, then yes this is the solution. But how often do you need to deal with that?

All-in-all I can't see what you gain from this approach?

thorpe

all valid points, i guess. its just the way my cms is designed. some clients dont need any userdetails, others want to be able to add email,firstname,lastname,address and ph, all with the click of a button.

loops aren't really required. say i have a user logged in, and i want to get there email addres. i simply run something like...

"SELECT val FROM userdetail WHERE user_id = '{$_SESSION['user_id']}' AND key = 'email';";

ps;

When your query returns multiple users, you end up with loops within loops.
Can't get an easy count of users from the result,

no query should return multiple users, and to get a user count, id'e just query the users table.