Say I have a table called "person" with a column called "age" where age can be an integer or a NULL value.

My question is what's the RAM and performance difference between having a column in a table that has a NULL value, for say 50% of the rows, or having a separate table (of that column) with the primary key from the other table (create a table called "age" with the "person" primary key in it, but having no NULL values in it)?

Performance: It will be quicker with the NULL value because you don't need to join with another table to get the age.

Space: A NULL value takes up one byte (depending on system) to store so you'd have to add this to the storage requirements. If you're using a separate table then you'd only be storing the values you know and not the Nulls however you'd also need to store the id of each user alongside each age in the new table. RAM is a different type of memory to what the database is stored in.

Complexity: Your code will be more complex if you use a separate table.

Each user should have an age unless it's unknown so it should be stored as a simple field in the user table and allow NULL. You could always force the user to input a birth date and get round the issue

Depends what you are doing - there are lots of different operations, some of which would perform better and some worse depending on what you do. Most practitioners would just have a NULLable column, relational purists are more likely to go for the one to one relationship.

BTW - even more important is you don't ever store age, not unless you are keen on keeping a team of staff employed 365 days a year incrementing 1/365th of the table's values every day.

pootle flump ur codings are working excelent.

Cool thanks.
Sorry the Age column was just a poor example that I chose. I don't actually have a column for age.

One more question though. Say I had like 5-10 columns in one table that could have a lot of NULL values. Is there a difference then?

Let me be more detailed. I have a table that has about 2-3 columns that are "NOT NULL" and about 8-10 columns that could possibly be "NULL" based on if the user wishes to fill out that information. This table will often (almost always) be JOINED with a few other tables. Currently I'm developing the table with just "NULL" columns but I'm thinking about just creating a new table for each so that in the long run (if I have to add to the table) I can just create a new table instead of adding a new column.
Thoughts?

Also, this table should eventually have a ton of rows and will be my largest table of the database. I'd say it could eventually have about 400-800 rows inserted daily.

Keep your design simple, and keep all attributes of an entity in one table, regardless of whether those values might be null.

Without knowing more about your application I'd say exactly the same thing as before. It's quite ordinary to have joins on a few tables but if your 8-10 columns are moved out into separate tables then your queries now not only have to join with your existing "few extra tables" but with another 8-10 extra tables as well. This will definitely be inefficient!

ever notice how many actual databases relational purists build?

answer: they're too busy ranting about the joys of relational algebra on newsgroups and discussion forums

i should know, i once made the mistake of admitting in public that i *heart* NULL

bit NULL -- that's gotta be a classic!!!

i lolled

DBA humor. Cracks 'em up every time.