Repeating a variable length string over many rows (both data and indexes) is far less efficient than storing a tinyint value.
- The string "Fairy Tale" takes 12 bytes at least on most systems including 2 for length.
- You have denormalised and added data modification anomaly risk
- You then have case sensitivity and collation to take into account for comparisons
Edit:
Your main problem is database size and bloating because rows are longer then necessary.
This means less rows per page and more memory use for queries. See these for why
I've seen huge databases that don't use lookup tables (designed by Hibernate ORM on MySQL) and have long strings repeated. By my estimate, the database could have been 60% smaller at least.
Normalisation isn't an issue if you are using lookup table on the natural key. Which you have clarified
Please consider this as an alternative. The previous two examples will both require that you make changes to the schema as the application's scope grows in addition the "custom_column" solution is difficult to extend and maintain. Eventually you'll end up with Custom_510 and then just imagine how awful this table will be to work with.
First let's use your Companies schema.
[Companies] ComnpanyId, COMPANY_NAME, CREATED_ON
Next we'll also use your Users schema for top level required attributes that will be used/shared by all companies.
[Users] UserId, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CREATED_ON
Next we build a table where we will define our dynamic attributes that are specific to each companies custom user attributes. So here an example value of the Attribute column would be "LikeMusic":
[UserAttributeDefinition] UserAttributeDefinitionId, CompanyId, Attribute
Next we define a UserAttributes table that will hold user attribute values
[UserAttributes] UserAttributeDefinitionId, UserId, Value
This can be modified in many ways to be better for performance. You can use multiple tables for UserAttributes making each one specific to the data type being stored in Value or just leave it as a VarChar and work with it as a keyvalue store.
You also may want to move CompanyId off of the UserAttributeDefiniton table and into a cross reference table for future proofing.
Best Answer
Enumerated types should be a separate table in your database that have an id number and a string name and any other columns you might find useful. Then each type exists as a row in this table. Then in your table you are recording the transactions the "trans_Type" field should be a foreign key to the key of that reference table. This is a standard practice in database normalization.
This way you have stored the one official name string, get to use number comparisons for performance, and have referential integrity that every transaction has a valid type.