1

Some background:

Users submit a form that has a category. 

If (category has manager) {
send manager email
}
else {
send 'default' manager an email
}

For the sake of argument, manager_category has 2 columns; Manager's Email and Category

Admins can update the 'default' manager.

Whats the best practice for storing the 'default' manager?

Imo, adding another row in manager_category with 'default' as the Category is best.

Another option is to create a new table with 1 row and 1 column.

What is the best way to store this value?

EDIT: I should add that there's 4000+ categories. Most of them don't have rows.

Tuna
  • 21
  • 4

2 Answers2

2

I have worked with databases that had a settings table, which was table with one row and a lot of columns, where each column represented a value that was configurable, and should hold true everywhere in the application.

As I believe most DBMSes have an upper limit on the number of columns a table can have, there are limits on this, but it can be a practical solution for setting what amount to configurable global constants for the application.

That said, given that there is a logical place to store this information, I would agree that your suggestion (store it with the rest of the manager data, and a special category (default)) makes perfectly good sense, and should make queries a bit easier to write, and to follow later.

RDFozz
  • 11,304
  • 4
  • 20
  • 36
1

After much deliberation between coworkers, we're going with the 'default' category.

Assume the default row has 

{
  objectID: 0,
  objectCategory:'default',
  user_id: 5
}

Here's the proc we're using:

SELECT TOP(1)
  a.email
FROM
  item_approver ia
INNER JOIN approver a
ON a.user_id = ia.user_id
WHERE
(
  ia.objectID = @objectID
  AND ia.objectType = @objectType
) OR (
  ia.objectType = 'default'
)
ORDER BY ia.objectID DESC

The function always returns the default email if none can be found and we did it with only 1 Select statement.

Tuna
  • 21
  • 4