"chicken or the egg" => "database first vs code first"
With all the efforts done to decouple applications from storage (database) you could say that I should write code first. That is correct, just that I want to see how easy is to write a Symfony bundle starting from an existing database, like when you have an old application and you want to migrate it to Symfony.
I extracted from stackoverflow two advices on the process of building an application:
1. Begin by writing down every function of your site. Define which objects you'll need and sketch the relationships between those objects. Design your database only when you have this information ready. It makes it a lot easier to see which tables are needed (generally one table per object type) and which relationship fields you'll need.
2. Your logical data model should effectively capture the business requirements of your application. Your physical database design should be based on the logical data model combined with the necessary changes that you as a DBA feel are needed to maximize the efficiencies of your RDBMS.
If you are finding that you have to make numerous changes to the underlying database design through out the software development life cycle of your application it is indicative of two things:
- Scope creep - You're allowing new requirements to be introduced at an inappropriate time.
- Insufficient Business Requirements - Your data modeler(s) (or system analysts) did not sufficiently translate the requirements from the business analysts. This resulted in an incomplete or incorrect data model to support the requirements of your application.
That being said once an application has been turned over to production it is not uncommon to have to go back and make iterative changes to the data model to support the natural evolution of the application or underlying business processes.
OK, so I will first define the business requirements and after this is done design the Entity-Relationship Diagram. Another question arises, should I use foreign keys? InnoDB vs MyISAM?
Another piece of wisdom from stackoverflow users about foreign keys:
"A database schema without FK constraints is like driving without a seat belt."
"Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default. can't imagine designing a database without foreign keys. Without them, eventually you are bound to make a mistake and corrupt the integrity of your data. They are not required, strictly speaking, but the benefits are huge. "Below an argumentation on using InnoDB taken from here:
http://stackoverflow.com/questions/7492771/should-i-use-myisam-or-innodb-tables-for-my-mysql-database?rq=1
"Always use InnoDB by default.
In modern versions of MySQL, that is 5.1 and 5.5, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.
The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.
There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.
Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:
Virtually the only reason to use MyISAM in current versions is for FULLTEXT indexes. And that's due to be supported in InnoDB in MySQL 5.6 (update: indeed InnoDB supports FULLTEXT in 5.6, but it's pretty buggy still, as of 5.6.13)."
- InnoDB is more resistant to table corruption than MyISAM.
- Row-level locking. In MyISAM, readers block writers and vice-versa.
- Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
- MyISAM is stagnant; all future development will be in InnoDB.
