On Mon, Jul 25, 2016 at 5:51 PM, shg_sistemas@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

Hello! I  have a FB 2.5 database with a tabla "invoices". I need to add a field in that table which will be null most of the times. Lefts say, that 90% of the records will have this new field in null. 

What is your advice? To create a new table with a FK to "invoices" and save the needed field in a separate table (only when the value is not null of course)

Each record in the second table would incur nearly bytes of storage overhead.  The outer join to put together the full record can make query optimization difficult.  Really, that's a bad idea.

 

or just create the new field in the main "invoices" table? I think I read somewhere that null fields use very little space in the database. Is that true?

Null fields take up very little space.  Every record has space for a null flag for each field in the record.  The space is there whether or not the field is null.  A null field is filled with spaces or zeros, depending on the type. Run-length compression will probably make it disappear.

But seriously, people, don't worry so much about data size!  The basic ODS on which Firebird is built was designed in 1984 - thirty-two years ago!  Back then, DEC, where Jim and I worked, had three database groups.  We got together and asked management if there was some way we could get a gigabyte of disk to do some performance and limits testing.  Management said, "NO.  Too expensive.  No real world application will see those limits."  DEC made disks and our group was part of disk engineering.  Now you can get 5TB for $120.  Yes, dense records reduce the amount of I/O, but remember that Firebird was designed for small disks and almost no memory - it's miserly about space.

Good luck,

Ann