What are the different kinds of mods?

Script Mods

Script mods are custom code scripts that add functionality. Some examples would be MCCC, UI Cheats Extension, and Wonderful Whims. These files will have a .ts4script file included.

Tuning Mods

A tuning mod is a package file that changes the default ways in which the game operates. Some popular examples would be no singing in the shower, meals that are group servings default to some other number instead of 8, no (fewer, or more) zombies spawning, more difficult (or easier) homework, etc. ( Taken from this Sims Forum question). Tuning mods are typically in a .package file format.

Overrides

An override mod is a package file that overrides EA functionality. These can be tuning mods, but aren't limited to them.

For a more in-depth view of mods, I highly suggest reading this article by Carl Sims.