2

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?

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.

  • That is a good catch about finding out the non-preferred names. – David Bullock Mar 19 '15 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.