Strings or Integers for Storing Types in Database
Shadowdare
MVP
I have a table called Article with a column called Status. In the code, the status value is used to indicate whether an article is in draft, pending review, or published state.
I can store those values in the database as integers defined in the code, or use an enum, with 0 for draft, 1 for pending review, and 2 for published. This way is faster to query with, but it is not readable for humans. If a current type needs to be changed or another type needs to be added, then the values would have to be updated accordingly.
On the other hand, I can store those values in the database as strings defined in the code with Draft, Pending, and Published. This way is readable for humans and slower to query with. If a current type needs to be changed or another type needs to be added, then there probably won't be as much as an issue as with the integer way.
As an alternative, I can use the CHAR type with d for draft, r for pending review, and p for published, for example.
Which way would you use? Which way seems the cleanest and best able in the long run for scaling web applications?
Add Pages to Vanilla with the Basic Pages app
Comments
When looking at the vanilla tables I see strings: Discussion.Format is Html, BBCode, Markdown, Text and User.HashMethod is Random, Vanilla
So I would guess, it seems to be more the Vanilla way to use strings.
That said, I would use enum because that is what that type is made for and the database doesn't need to be human readable.
Personally, I go for integers every time I can, mainly because strings gave me too many headaches in the past. Case-sensitive systems and typos are always around the corner, and you can bet anything that someone will enter data manually in the table, messing things up. If you really want to make things human readable, then you could fetch the integers and translate them on the fly, before displaying them. This will also allow you to use localisation functions and present the information in multiple languages, if you need.
My shop | About Me
I would not use the char type as it doesn't increase readability.
Is the performance hit on mysql enums really that high?
Search first
Check out the Documentation! We are always looking for new content and pull requests.
Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.
Yes, @R_J and @hgtonight are exactly right, use
enumor a string (varchar). We addedcharsupport basically just for folks using the framework for their own projects. I don't recall ever seeing it used in Vanilla.The database should always be human-readable. If you're using a good GUI SQL editor like Sequel Pro the enums are perfectly readable.
Vanilla never-ever does single-letter (or really any) abbreviations for data storage (column names, variables/properties, etc). You shouldn't need a seance to decipher our data structure (YES VBULLETIN I'M TALKING ABOUT YOU AND YOUR BITWISE OPTIONS STOP TRYING TO AVERT MY GAZE).
Remember that our database structure maps directly to properties because of how our framework works, so if you used char then you'd have code floating around where
$Article->Status = 'd'.Great question!
And @businessdad brings up a great point: Always strtolower before doing string comparisons.
Wherever you find integer being use to show status in Vanilla where it goes beyond 0 & 1 (like the 0,1,2 for Announce and the 0,1,2 for Admin flag) it's an architectural mistake we made. Those should've (and would've) been strings if we'd realized the additional '2' case that was added later.
Note:
enumfields are not portable. If you have the luxury of being able to choose the target database, then you are good to go, but I never had it in my life. Every database structure had to work seamlessly on (almost) any RDBMS, therefore only common data types were allowed, and same rule applied to SQL queries. Because of that, I tend not to use any proprietary data type.Or use integers and avoid the issue.
I still prefer integers even for multiple statuses. With a simple database view, readability is not an issue, and integrity is easier to maintain (at least, in my experience).
However, I have to add that there is a very good "rule of thumb" for choosing between integers and strings: if you are not using the field value for mathematical operations, use a string. That applies mostly to fields like house numbers or phone numbers, but it's generic enough to be reusable.
My shop | About Me
Since we're talking about a Vanilla addon and not a generic framework application, I think it's safe to assume that portability to another engine isn't a top concern.
It's a relatively safe assumption. I've been asked to check if it's possible to port Vanilla to SQL Server no more than a couple of weeks ago. I've also been asked to move it to a NoSQL database, but I gave up.
My shop | About Me