Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

Question

In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the names is considered 'canonical'.

Two appealing options for representing this would include:

1. a single table, with a Preferred column (a boolean flag) and a 'filtered unique index' on BusinessId, Preferred(when true) to ensure that for any given business, only one of the names could be preferred, but all others could be unpreferred

   BusinessNames
   
   BusinessID | Name          | Preferred
   --------------------------------------
   1          | Sony Pictures | 1
   1          | Oh no! Inc    | 0
   1          | Facepalm Co   | 0
   

   ; OR

2. the plainer version of that same table (BusinessID, Name) and a peer table for the 'preferred names' (also BusinessID, Name, but unique only on BusinessID)

   BusinessNames                       PreferredBusinessNames
   
   BusinessID | Name                   BusinessID | Name
   --------------------------          --------------------------
   1          | Sony Pictures          1          | Sony Pictures
   1          | Oh no! Inc    
   1          | Facepalm Co   
   

I lean towards option 2, which seems cleaner to me. Never mind that BusinessNames could reference a surrogate primary key in a Names table for possible space savings. (I am going on an anti-surrogate-key binge right now to see where I end up).

I am aware of some implications for my app, such as:

• To insert/update/delete a name, I have to touch 2 tables.
• To do a query where I want to return only a single row per business and 'the name' of that business, I would simply involve PreferredBusinessNames, and leave BusinessNames alone.

I think I can live with those implications, but am I shooting myself in the foot by this sensible extreme normalization'? Am I creating a monster, or will I be pleasantly surprised? I've never seen anyone else do it that way, and surely there is a reason?

Answer 1

It depends. There are cases whether one design or the other are satisfactory.

If you are doing a lot of queries only on the preferred or default entry, then the second option seems better.

If, however, you are normally doing lots of queries for all the entries, regardless of whether they are preferred/default or not, the first option is a little easier to set up and manage.

And if most of your queries want to retrieve all the entries except the preferred/default one, you'll find the first option a lot easier. I can't think of such a case off the top of my head.

And, in most cases, these tables are so tiny that performance considerations will be secondary.