HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Strings or Integers for Storing Types in Database

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

  • R_JR_J Admin

    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.

  • hgtonighthgtonight MVP
    edited May 2014

    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.

  • LincLinc Admin
    edited May 2014

    Yes, @R_J and @hgtonight are exactly right, use enum or a string (varchar). We added char support basically just for folks using the framework for their own projects. I don't recall ever seeing it used in Vanilla.

    @R_J 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.

    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'. :s

    Great question!

  • LincLinc Admin
    edited May 2014

    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.

  • @Linc said:
    Yes, R_J and hgtonight are exactly right, use enum or a string (varchar).

    Note: enum fields 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.

    @Linc said:
    And businessdad brings up a great point: Always strtolower before doing string comparisons.

    Or use integers and avoid the issue. :D

    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.

    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.

  • LincLinc Admin

    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.

  • @Linc said:
    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. :D

Sign In or Register to comment.