CODEWITHSUNDEEP
mysqldatabase
CruisinCosmo
CruisinCosmo

Reputation: 1157

Post Type in Database - INT or VARCHAR

I'm building a forum type site in which posts will have different types: Discussion, Question, and Comment.

What would be considered the preferred method of storage for both performance and readability?

For instance:

Id  |  PostType  |  PostTitle
1      2            Example title

or

Id  |  PostType  |  PostTitle
1      Question     Example title

Performance is my main goal, so my first idea was to store it as an INT. However, to get the post type name (which I need every time) I have run a rather unenjoyable if/else statement.

I would prefer the VARCHAR type, as it avoids the if/else business and remains readable, but I am uncertain.

Upvotes: 0

Views: 417

Answers (6)

shashuec
shashuec

Reputation: 694

Use Enum.You will be sure then you don't have any typos.Plus you can compare enums using strings and they will be stored as INT.

Upvotes: 1

jm_toball
jm_toball

Reputation: 1217

I would use integers to keep the amount of redundant data low. You could just use an array or map to get the right String for each id, e.g:

cats = {1: "Question", 2: "Comment", 3: "Discussion"}
cat = cats[post.postType]

If you limit the varchar field as suggested by John you might run into problems if you want to have longer names later.

Upvotes: 1

Loogawa
Loogawa

Reputation: 389

A lot of companies will use an int for categories. I guess they think it helps make if statements shorter and a select case statement is easier. That being said I greatly prefer Varchar. With varchar you never have to worry about excessive documentation or new coders not knowing which number corresponds to which number. 

if(PostType == 1){do code}


if(PostType == "Question"){do code}

which one of these is easier to understand? The top one would require a few lines of comments. Which doesn't sound like that big of a deal but if you use the code a lot it can add up.

Upvotes: 1

reggie
reggie

Reputation: 13721

I would advice you to have a "Type" table which will have the post type information like Question, Answer and Comments. In the main table you need to call it using its primary key, thats an integer value as per your first example. This will help you manage the database incase you need to add more post types in your table.

In case of readability, you can create a view joining the above 2 tables and can read it whenever you want to.

Upvotes: 2

John
John

Reputation: 2792

In modern db systems, there isn't much of a need to worry about performance on such a small scale as that individual field there. However, if you choose to make it varchar, limit the varchar length to an appropriate size varchar(10), for example. You may also choose to create a lookup table to hold you question types. Though this is unnecessary and probably slower.

My choice would be to place the type in the field as you have it in your second example.

Upvotes: 1

FreeAsInBeer
FreeAsInBeer

Reputation: 12979

If you almost always need the text version of the PostType, then you should probably use the VARCHAR type to avoid additional tables and extra code.

Upvotes: 2

Related Questions