Systems Involved

Parameters for spell item and attunement

Magic for basic spell information
EquipParamGoods for spell item information
ShopLineupParam for attunement
EquipMtrlSetParam for attunement

Parameters that determine what a spell does

Bullet for projectiles
SpEffectParam for other effects

Other Systems

FMG is used for text data. Spells require modifying the text data files for magic names, magic descriptions, and item names.
TPF is used for icons.
EMEVD is used to make the attunement menu appear at bonfires only after you own a spell.

Workflow

Create new IDs for Magic, EquipParamGoods, EquipMtrlSetParam. They should all have the same ID, and it's recommended you pick an ID between 3000 and 5910, which is the vanilla range of IDs. This tutorial will be referring to this ID as the "Basic Spell ID" for convenience. It's also recommended you duplicate pre-existing spellIDs used for other spells, as many fields will be identical.

EquipMtrlSetParam

Field materialId01 should be the Basic Spell ID.
itemNum01 should be 1

ShopLineupParam

The ID should be within the 10000-10099 range, as this is the range defined in Bonfire talkESD shop ranges (this can be modified if you want a higher range).
mtrlId should be the Basic Spell ID.
shopType should be 2.
equipType should be 4.

EquipParamGoods

magicId should be the Basic Spell ID.
goodsType should be 5.

Magic

Determines most basic information about the spell, so many fields need to be modified.
The most important fields are refCategory and RefID, which determine which Bullet or SpEffectParam to use for a spell's actual effects.

Bullet or SpEffectParam

Determines actual spell effects, so many fields need to be modified.

FMG text data

The following text types need an entry, with the ID being the same as the Basic Spell ID:
Magic names, magic descriptions, and goods names.

EMEVD map events

Event 719 in common.emevd should be modified to include a new check for ID of spell. This event is used to make the attunement menu appear only after the player has obtained at least 1 spell.

Additional Information

For more detailed information on the many different systems involved with magic, see http://soulsmodding.wikidot.com/tutorial:how-to-create-new-spells-in-ds1