Planning and designing web app structure and logic

Question

I've been building a web app for over a year now. It was the first thing I started building as I learnt PHP and MYSQL etc. It has grown from a very simple budgeting app coded in raw PHP to a full personal finance tracker that I use everyday running on Codeigniter. I have built a couple of other small web apps in the mean time and as a result I am 1 year more experienced than I was when I first started this project and I am think my development is starting to suffer as a result of the fundamental design decisions I made on day 1.

Although the code has been rewritten to work with codeigniter the database structure is still the same and I feel I am having to write more code than required to get round issues with my DB schema.

My questions are:

1. Are there any good resources for learning how to effectively design databases? Books, courses etc.
2. Back when I started this project I very much "designed as I coded" but I feel that perhaps the more sensible way is to design everything at the function level so that you can see what the flow and logic is before writing a single line of code. Is there a particular name for this type of development or is it just standard practice? (I have no formal training in web dev so I feel I may just be stumbling into something that everyone already knows)
3. Should I design my logic and functions and then my database or start with database and then logic and functions?

Answer 1

1) Are there any good resources for learning how to effectively design databases?

Learn about entity-relationship modelling. The ERwin Methods Guide is a good start.

But in the real world, databases don't just store data - they interact with client applications and the performance needs to be good. Use The Index Like! is a great introduction on the topic.

2) ...

Ideally, you'd have a fixed set of requirements, then design a model based on them, then write a code based on the model. Unfortunately, changing requirements are a fact of life and this this "waterfall model" is fragile in the face of changing requirements.

On the opposite end of the spectrum is the "agile" software development, emphasizing change.

The truth is that there is no sliver bullet and the optimum is usually somewhere in between.

3) Should I design my logic and functions and then my database or start with database and then logic and functions?

Data tends to live longer than code. So I'd concentrate on getting the data model right first. If you have to make a decision between cleaner data and cleaner code, choose cleaner data.

That being said, try to make your data model "evolvable". Don't go to extremes such as implementing EAV where it is not actually needed, but do the sensible things such avoiding redundancies through normalization, picking natural vs. surrogate keys sensibly, not artificially limiting the number of items etc...