I usually fly by the seat of my pants when building my databases. However, my new project is going to need quite a bit of planning. I never went to school for database development so I have no formal training on the planning process.
Is there any good software, methodologies for planning these things out?
Thinking out usage cases and example code ahead of time has been a great help to me in database design; it's a nice way of testing the database's integrity without writing a single SQL statement.
Good luck!
This is one place where the repository pattern can help out a lot, I use that pattern when I have a good idea of how I want the software to work, but when I lack a clear idea of the data involved. Generally it is much easier to create/refactor the mock objects than it is to modify tables and the backing stored procedures/queries.
You can separate tables onto different disks to speed up access (I assume mySQL can do this). You can get high speed disks.
Maybe you mean large as in, lots of tables. That's a pretty big topic, but you can start with these:
Some developers which fly by the seat of their pants don't do such things. Kudos for thinking ahead.
Visio can do relationship diagrams. So can paper and pencil.
Here is some info on the data modeling subject: http://en.wikipedia.org/wiki/Data_modeling
Stick with the first few normal forms when designing your schema if you don't know what you're doing. Odds are it will allow you to make changes easier than any other method when you realize your design mistakes later on.
When in doubt, feel free to ask for opinions. The easiest method of visualizing a database design is to use Entity Relationship Diagrams (ER Diagrams) and it also allows us to easily see what your design looks like without sifting through code.
Drawing things out in E-R Diagrams can help with managing the complexity.
Edit: Let me add that there are also rules/guidelines to help translate E-R Diagrams into relational schema, and there are also tools to aid in the process.
Think about schema versioning. How are you going to handle changes to the schema of the database over time? Do you need to migrate or upgrade data? Can you throw away data during development?
Have separate instances of the database for test, staging and live - from early on.
Draw lots of pictures.
It sounds like you would already know this: but associative entities (http://en.wikipedia.org/wiki/Associative_Entities) are often required for DB sanity.
I'd start with a naming convention. I use *_NM (for name) and *NUM (for numbers), V* for views, etc.
Nothing earth shattering, but it does make your job easier when you can guess your own table names and row names without having to look them up. Doesn't matter what you choose, just make sure it's sensible and consistent. Most professional DAs use only uppercase for table names.
Personally, I like to use ID for IDs on every table that has an ID (which is typically a PK), then for the foreign key relationships as _ID to represent the relationship. For example,
Table SCHOOL has a PK of ID.
Table STUDENT has a PK of ID and a FK that references table SCHOOL, SCHOOL_ID.
So, when looking at the student table without any ERDs, it's easy to see that SCHOOL_ID references SCHOOL.ID and it looks good when reading the SQL statement, too.
With regard to the data modelling tool, Erwin: http://www.ca.com/us/data-modeling.aspx
So, before I offer any advice on how best to design a large schema, I need to ask one question: Is a large schema absolutely necessary?
You asked if there are any good software methodologies for planning large systems. Indeed there are, and one of the best approaches to complex software development is SOA: Service Oriented Architecture. If you wish to educate yourself a bit on SOA best practices beyond the database level, I highly recommend looking into Thomas Erls books, notably his SOA: Principles of Service Design. I also highly recommend listening to some of Udi Dahan's lectures on service-oriented and domain-driven design and architectures. Lot of good knowledge to be had from both of these guys.
When it comes to databases, before you dive in and develop a very large, complex schema, make sure you really, really truly need it. In a service-oriented environment, the motivation is to identify distinct, unbreakable boundaries between the distinct services of the business problems you are trying to solve. Once you have identified these boundaries, you should find that there are smaller schemas that can be created within them. Sometimes this leads to data duplication, as information must be published from one service to another when it needs to cross boundaries. But the benefits of having several smaller, less complex schemas can be huge. You gain greater autonomy, portability, flexability, and maintainability than you have with a single monstrous schema.
Look into SOA, particularly how to handle databases in a service-oriented architecture. The following presentation given by Udi Dahan should also provide some very useful insight:
Download and build your database using MySQL's Workbench tool. Will help you build and maintain the database.
Don't fall into the trap of trying to design everything up front. It simply cannot be done. As the project proceeds, you find better ways of implementing the features that you already had implemented. And as you gain experience from you project, you also gain insight on the domain, and how to best design the database.
I would recommend taking an agile approach. Design a litte bit at the time. And when you see that stuff you already created could have been designed in a better way, refactor that. This goes both to code and database schema.
One word of note however. Where it is easy to refactor the business logic (unless you place the business logic in the database - which you don't. Right?) after launcing the application, refactoring the database after launch is considerably more difficult because you have to maintain data. So if you need to move one field from one table to another, you need change scripts.
So when you near a launch, it might be a good idea to plan a little ahead. But in the early phases of development, I would definately recommend taking an agile approach. Create one table at a time. One field at a time.