Best practices for database logic in or out of database. Save logic in database? [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 4 years ago.

Improve this question

Best practices for database logic in or out of database. Save logic in database?

What is the best pr开发者_JS百科actice as far as saving enumerations and other lookup data in or out of the actual database? For instance in a web store is it okay to save all of the products if you are still going to write code to put the data into and out of the tables that use this product information. What if you had user information like roles (manager, employee, etc). Would it make sense to have a lookup table for the roles or can your CRUD logic keep all of that and when a new user is added/updated the CRUD code can do that validation?

This may or may not be a community wiki that is fine if it needs to be tagged as such. I really just want more information and to know what others are doing.

EDIT: Great answers. And the consensus seems to be yes, put the constraints in the database. So my next question is what is the technical mechanism to make that happen. If I have a "roles lookup" table, and I go to add a new user. How do I say, the roles column for a new user must be one of any of the values in that lookup. I know how to do this in code but what is the SQL mechanism to do this?

---

The database is for data, and validation rules that enforce the integrity of that data.

To answer your specific question, yes, I would store users/roles in the database. There is no case in which I would want to have to update code in order to add users to the system.

---

The database is the place to enforce any logic that must be enforced to ensure data integrity. Doing that only in the application is a recipe for disaster, databases are not changed only by the application.

In part you need the lookup tables to ensure data integrity, so that values which are not part of the lookups cannot be added.

To answer your second question, look-up foreign key constraints.