If you’re a developer for a small to mid-sized web application, one of the very first questions you’ll be faced with is, “How should the database be structured?”
If you’re new to the world of relational databases, understanding structures can be very complicated. The first thing you need to do is understand that in general, more tables are better than less. If you’re doing a member directory, for example, you may find yourself putting name, addresses, phone numbers, etc. into one table. Resist the urge! It’s very easy to pull information from multiple tables into a single SQL query, and you’ll be thankful later when you have the flexibility to add information you didn’t think you needed.
On the subject of flexibility, try to anticipate the future needs of your application. You may want to sit down with the people that will use your application, and ask them to tell you everything they might need to do with the information. Have them walk you through their work (or volunteer) day, so you can see where inefficiencies can be improved by your application. Ask them what the company/organization’s future goals are. Take lots of notes – the more, the better.
Oh, and one more thing. Don’t ever, ever duplicate data. You’ll get headaches down the road. Trust me.