I have posts table in db
post can be draft, published and review, what is the best and most used way by developers to store this in db? should I use enum column type with these 3 values or should I store this as integers for each status post can have? What would you do and how would you store it and later access it and display it on the frontend?
I like to use 3NF normalisation, so I would use an integer and create a separate table for the post statuses which contains the status ID as the auto incremented primary key and then perform a join when you need to display the status.
If your status values are never going to change, use enum. Otherwise use integers (and define const variables in your model class to represent the values of these integers).
If however, there are going to be very frequent additions/changes/deletions to your states, use a separate table for storing possible values. Note that this would however reduce performance in the event of infrequent changes because of joins
Refer: this article and post for more info.
In your table with integers exp 0|1|2, and make new table post_type
id | yourtable_id | type
0 0 draft
1 1 published
2 2 reviewd
and use join to get their names
I would create a new table to save the available status values with a tinyinteger unsigned autoincrement field as id. Then I would use the id values in your post table to associate the status to the posts. It is the best way to maintain and update your data in the future.