1

I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but as I am building my own I want to make sure I am thinking in best practice. Now many applications have some shared concenpts so was wondering if there is some cheat sheet for common attributes paired with data types and why. Here are some expamles of what I was curious about:

  • table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?
  • Entry title - VARCHAR or text?
  • Entry body - I assume text.
  • 5 Star rating - INT or Decimal? Why since I have read about both?
  • Phone & Zip - INT or VARCHAR?
  • Event Date - Again, Date or Timestamp

Thanks. I know this seems pretty basic, but just trying to an idea of best practice and many of these items are exchangable between applications.

1

This is all IMHO:

•table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?

Use a timestamp - better for multiple mods in a given day.

•Entry title - VARCHAR or text?

You're not going to lose much with a text over VARCHAR, but I think that VARCHAR is the reasonable choice.

•Entry body - I assume text.

BLOB.

•5 Star rating - INT or Decimal? Why since I have read about both?

How flexible do you want to be - I'd tend to Decimal.

•Phone & Zip - INT or VARCHAR?

VARCHAR - intl codes, extensions... more flexible.

•Event Date - Again, Date or Timestamp

Again you're not losing much adding the time - never throw away information that you may use later.

0

DATETIME vs TIMESTAMP -- If everything is always in the same timezone, then it does not matter. Across timezones, it may matter. DATETIME is like taking a picture of a clock; TIMESTAMP is an instant in the universe. If I insert noon in a DATETIME, it will say noon for you wherever you are. But inserting noon into a TIMESTAMP will show up differently on the other side of the world. Which effect do you want?

Large text fields should be TEXT or MEDIUMTEXT. If you plan to have multiple languages, use CHARACTER SET utf8mb4. Short text fields should be VARCHAR with a suitable maximum. (There are slight advantages of VARCHAR over TEXT.)

Small integers -- TINYINT UNSIGNED -- That is the smallest; it allows values 0...255. (Sufficient for 5-star rating.) If you need an "average rating", that is another situation. Suggest FLOAT, and be sure to format it as desired (such as '4.33').

Phone numbers, zip codes, country codes, etc, etc. -- VARCHAR(...) CHARACTER SET ascii. Use a suitable length and use ascii to override the utf8 that you might have on the table. For truly fixed length fields, such as 2-letter country_code, use CHAR: country_code CHAR(2) CHARACTER SET ascii.

For small lists of options, use ENUM. Example: gender ENUM('unknown', 'Male', 'Female') NOT NULL.

Specify NOT NULL wherever appropriate.

For most numeric values, use UNSIGNED.

For money, use DECIMAL with a suitable number of decimal places (2 for most countries, 0 or 4 in some countries).

Never use (m,n) with FLOAT or DOUBLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.