SQL Anti-Patterns

Post by Breza » Mon, Oct 27, 2025, 6:51 PM UTC

SoftTalker wrote: post #72

magicalhippo wrote: post #66

Earlier quoted context omitted.

> Also, understand how your DB handles nulls. Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed. If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

I've found it's easier just to keep things NULL in many cases. Sure, if you have a student database with the column favorite_sport, maybe it makes sense to use the value "Not a sports fan." But think about an email list with the column unsubscribe_date. For active recipients, you'll want that field to be NULL.

Post by Breza » Mon, Oct 27, 2025, 6:53 PM UTC

magicalhippo wrote: post #205

solumunus wrote: post #195

Earlier quoted context omitted.

Over 90% of my optionals have their own tables and it’s the cleanest and most maintainable database I’ve ever worked with. I will always design databases this way going forward. That’s my experience. I remember working on ERP systems with 40+ column tables, most of which were null. With no clear constraints on which options should or shouldn’t enable or make mandatory other options. This becomes incredibly obvious an…

> I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen wo…

This sounds like the kinds of healthcare claims data that I often analyze for work. Just in case you have a team of seven doctors working on you at the same time while you're in hospice, they keep a column for just about everything.

Re: SQL Anti-Patterns

Re: SQL Anti-Patterns