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.

closed as too broad by LowlyDBA, Erik Darling, Mr.Brownstone, McNets, mustaccio Jan 28 '18 at 21:22

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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.

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.

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