Tagged: Database

Interesting Web Dev Things 30 May 14

Lots of NULL’s in database tables

When it comes issue of having nullable columns the DB design community is split (you’re either a big fan or normalisation purist). The argument against the overuse of nullable columns is two fold.

1. If the majority of rows in your table contains a columns which are mostly null then it’s wasted space (this is especially bad for large tables)

2. Application’s poor handling of nulls can result in exceptions being thrown.

Arguments for nullable columns:

1. High-levels of normalization results in a increase in 1:1 mappings, which can increase the complexity of joins.

2. I’m of the opinion that the application should handle all inputs gracefully (i.e. pre-condition for a unit of execution). The database is there to serve the application since that’s what the users are interested in.

3. Application flexibility – fields need a default value that’s future proofed and null provides that mechanism

In the end it comes down to your business requirements, there’s no hard and fast rules. Those in the enviable situation of having to work with small databases can go all out and null everything except the primary/foreign keys.