I am fairly new to database design, but I have managed to do the record store tutorial over at macromedia.com. I have also created a few simple cms designs, but I still consider myself a newbie.
Anyways, I use PHP and MySql..actually, I also use Phakt which uses ADODB as well. Here's my situation. I've taken on a job which requires quite a few fields of information to be saved and called upon for reports etc. There are about 65 fields total. I think this can be done using a flat-file db, but it's probably not the most efficient way, although it's the easy way out. I'm not too familiar with relational db's so please bare with me.
The db is for a real estate company. I have broken it down to about 6 sections.
Client info: name,address,email,phone,buyer/seller,DOB, notes etc.
Loan Payment info: loan ammount, loan type, loan program, & payments
Applicant status: green light, co-signer, alternative lender, dead green light, etc
Agent info: agent assigned, date assigned, date of appt, notes
Escrow checklist: sales price, office, client info, agent assigned, and 25+ other fields
Commission breakdown: commission percentage, TC commission, fees, etc
I know this is pretty complex, but I see a vision of how it will work. Problem is, I'm not quite sure where to start and how to make it efficient. I know I could bust a flat-file db on this, but after a few thousand records it probably will begin to bog. I guess I'm just looking for some type of direction and hopefully some tutorials or sample sites to refer to.
If anyone has any advice or links that could help me solve this, please let me know.
Not trying to cop-out on an answer, but you'll probably want to do some reading on relational database design or learn about ERDs (Entity Relationship Diagrams). That said, MySQL does not support some key issues for Relational Database such as 'referential integrty' although many of us tend to turn a blind eye to this....anyway, it sounds like you have a good start from the way you have broken out the key elements of information. You'll find that using a Relational solution will be scalable and cutback on redundant data.....