SQLAlchemy Category column

Question

I am using a SQLAlchemy database to hold data for a flask application. I would like one column in my database to represent a category (e.g. the possible categories may be A, B or C).

I have seen in documentation that this can be achieved by a simple relationship which relates two tables. One table to hold some live data (inclusive of a category ID and a category) and another table to relate a category id to the associated category. http://flask-sqlalchemy.pocoo.org/2.3/quickstart/#simple-relationships

Would this method be considered good practice for including some kind of "category" column in my database? Or is there a simpler/better way. In this case my aim is to prioritise simplicity while maintaining good practice (don't really need best practice if it entails too much complexity).

Additionally, if my category names will never change, is it bad practice to use a constant list of category names to compare input data with in order to validate it? If so, why?

Answer 1

This is more of an SQL question and it isn't related to Python at all.

Anyways, it is actually better to use a reference table as you first suggested.

In this case, a Category table with one-to-many relationship. This allows you to change category name, and enrich Category with more details (like description) that might become useful in the future.

The other way, using constant list, is considered a bad practice - especially using Enums. You can read more about it in this article: 8 Reasons Why MySQL's ENUM Data Type Is Evil

You can read more about this dilemma here.

Hope it helps.