The framework an application runs on will be replaced, upgraded, or reconsidered several times over the life of a business. However, the database schema usually will not. What gets modeled during the first weeks of development tends to stay that way for years. When technical teams talk about rewriting an application, the stated reason is often the framework, but the underlying problem is almost always the schema. Understanding what makes a schema durable, and what makes it brittle, is one of the most practical things a development team or technical evaluator can do before a line of application code is written.
Why Schema Outlives Framework
Frameworks evolve on predictable schedules. Symfony publishes long-term support releases with defined end-of-life dates. The same is true of PHP versions, JavaScript frameworks, and the tooling that surrounds them. Unfortunately, schema does not work this way. A database that accumulated two million rows of order history is not a versioning problem; it is a gravity problem. Moving it, reshaping it, or reinterpreting it at scale is expensive in ways that updating a composer dependency is not.
This is why the early schema decisions carry so much weight. A customers table that stores a single address field instead of a normalized address record will eventually block every feature that requires shipping to multiple addresses, managing billing separately from delivery, or handling international formats. These are not exotic requirements. They arrive reliably as businesses grow, and when they do, the schema is already loaded with years of production data.
What Effect has observed in 20-plus years of custom application work is that the applications with the longest useful lifespans share a few structural habits: they normalize by default, they treat operational metadata as first-class concerns from the start, and they resolve the multi-tenancy question before writing application code. Each of these is worth examining directly.
Normalization: The Default and When to Break It
Normalization means storing each piece of information once and referencing it everywhere else it is needed. A properly normalized schema has an orders table that references a customers table by foreign key, not one that duplicates the customer’s name and email into every order row. It has an addresses table that can be associated with a customer record independently of any single order, so that billing and shipping are distinct records rather than column pairs.
Why Normalize?
The practical reason to normalize is not theoretical purity. It is that denormalized data becomes inconsistent over time. When a customer changes their email address, and the update writes to the customers table but not to the 847 order rows where that email was also stored, queries that join across those rows start returning contradictory data. The application has to compensate for increasingly complex logic, and the compensation itself becomes a source of bugs.
When to Denormalize Deliberately
The exception case for denormalization is read performance. When a query joins across five or six tables on every page load, and that join becomes a measurable bottleneck, copying commonly read fields into a summary record or caching layer can be justified. This is a deliberate engineering decision made with evidence, not a default. The reason to denormalize should be written down: which query was slow, what the measured improvement was, and which application layer is responsible for keeping the denormalized copy in sync. Undocumented denormalization is the version that causes problems in year three when the original developer is no longer available to explain why the orders table has a customer_email column that appears to serve no purpose.
Two concrete schema patterns illustrate the difference:
Brittle: orders table with columns customer_name, customer_email, shipping_address_line1, shipping_address_line2, shipping_city, shipping_state, shipping_zip.
Durable: orders table with a customer_id foreign key to customers, a shipping_address_id foreign key to addresses, and an addresses table that stores a normalized address record tied to the customer independently of any transaction.
The second schema can handle multiple addresses per customer, address history, billing versus shipping distinctions, and international formats without a structural migration. The first cannot, and the migration required to move from the first to the second on a live database with existing data is one of the more expensive problems in web application maintenance.
Soft Deletes, Audit Trails, and Timestamps
Three categories of metadata belong in the foundation of any business application schema.
Timestamps
Every table should have created_at and updated_at columns, set automatically by the database or the ORM layer, never by application code that might be skipped. Doctrine, the ORM used with Symfony, provides lifecycle callbacks and the Timestampable behavior through StofDoctrineExtensionsBundle, which handles this reliably. These columns cost nothing to add at the start and are almost impossible to reconstruct accurately after the fact. Without them, answering basic operational questions like “when did this order record change” or “how many customers were created last month” becomes either an estimation exercise or a full application log archaeology project.
Soft Deletes
Most business records should not be permanently removed from the database when a user clicks delete. An order that was canceled still needs to exist for accounting and audit purposes. A user account that was deactivated may need to be restored. A product that was discontinued may be referenced by historical order records. Soft deletes implement this with a deleted_at nullable timestamp column, or a boolean is_deleted flag with a timestamp companion. Records with a non-null deleted_at are filtered out of normal queries, but the data remains intact and recoverable. Doctrine provides this through the SoftDeleteable extension. Applications built without soft deletes from the start eventually implement a version of them anyway, usually after the first time a record is needed back and is gone.
Audit Trails
For applications that handle anything regulated, financially significant, or operationally critical, an audit log records who changed what and when. The simplest version is an audit_log table with columns for the entity type, entity ID, changed fields (stored as JSON), user ID, and timestamp. More sophisticated implementations use event sourcing or dedicated audit libraries. The right level depends on the application’s risk profile, but the habit of capturing change history should be established early. Adding audit trails to an existing application that was not designed for them requires both a schema migration and a review of every write path in the codebase.
Multi-Tenancy: A Schema Decision, Not a Feature
For any application serving multiple organizational clients, multi-tenancy is the most consequential schema decision made at the outset. It determines how data is isolated, how the application scales, what security guarantees can be made to clients, and how complex tenant-specific customization becomes. It also has direct implications for how customer portals are structured, since a portal’s usability depends heavily on whether the data model cleanly separates what each client can see and do. There are three primary approaches, each with meaningful trade-offs.
Shared Schema with Tenant ID Column
In this approach, all tenants share the same tables. Every row includes a tenant_id foreign key that scopes it to a specific organization. A customer’s table in a shared-schema application might have 200,000 rows from 50 different client organizations, all in the same table, differentiated by tenant_id.
The advantage is operational simplicity. One database, one schema, one migration path. Adding a new tenant is an insert into the tenants table, not a database provisioning event. Reporting across tenants and operational monitoring is straightforward.
The risk is data leakage through application errors. If any query forgets to filter by tenant_id, it returns rows belonging to other clients. This requires disciplined use of query scoping, which Doctrine handles through Filters, but it depends on those filters being correctly applied everywhere. For applications handling sensitive data, this approach requires careful review of every query path and a strong argument that the operational simplicity justifies the risk.
Separate Schema per Tenant
In this approach, each tenant gets their own database schema (or database, depending on the RDBMS), while the application still runs as a single instance. MySQL supports multiple databases on a single server, making it practical to provision a new schema for each tenant without deploying new infrastructure.
Data isolation is stronger here. A query scoped to one tenant’s database cannot accidentally return another tenant’s rows. Tenant-specific customization at the schema level, adding columns or indexes for a single client’s needs, becomes possible without affecting others.
The cost is operational overhead. Schema migrations must run across every tenant database, which for 50 clients means 50 migration executions with 50 opportunities for inconsistency. Tooling to manage this reliably is necessary from the start. Cross-tenant reporting requires either a separate aggregation layer or queries that span multiple databases.
Separate Database per Tenant
At the other end of the spectrum, each tenant runs against a fully independent database instance. This provides the strongest isolation guarantees, enables tenant-specific database configuration and hardware, and makes compliance claims more straightforward when clients ask about data residency.
The operational cost is the highest. Database server provisioning, backup management, monitoring, and migration orchestration all scale linearly with the number of tenants. This approach makes sense for enterprise applications where clients are large, have compliance requirements, and are paying at a level that justifies the infrastructure overhead. It is not a sensible default for a mid-market SaaS application with a hundred small clients.
Effect typically recommends a shared schema with robust tenant_id filtering for applications where client data is not particularly sensitive, and the client base is expected to grow quickly. Separate schema is the right choice when clients require contractual data isolation or when tenant-specific schema customization is a product requirement. Separate database instances are reserved for enterprise deployments where compliance and data residency are non-negotiable.
Migrations as a Discipline
A schema is not a document written once. It is a living structure that changes as requirements change. How those changes are managed determines whether the schema grows in a controlled way or accumulates debt.
Doctrine Migrations provides the standard tooling for Symfony applications: each schema change is written as a migration class with up and down methods, versioned in source control alongside the application code, and executed in sequence on each environment. The migration file for adding a shipping_address_id column to an existing orders table documents why the column was added and what it connects to, in a form that can be reviewed, rolled back, and audited.
The discipline that makes migrations actually work is the same discipline that makes version control work: small, frequent, descriptive changes rather than large batches. A migration that alters eight tables at once is difficult to review, difficult to roll back safely, and difficult to debug when something goes wrong. A migration that adds a single column with a clear comment explaining its purpose is straightforward.
Two migration habits that repay their investment many times over: never make a destructive change (dropping a column or table) in the same migration that makes the structural change that precedes it, and always test migrations on a production-sized dataset before running them in production. A migration that runs in two seconds on a development database with 500 rows can lock a production table for twenty minutes on a database with five million rows.
Search Indexing: Where MySQL Ends and Solr Begins
MySQL handles structured data queries well. It is less well suited to full-text search across large datasets, ranked results, faceted filtering, and the kind of search experience users expect from product catalogs, document repositories, or customer-facing search interfaces.
Apache Solr is the search indexing layer that Effect uses when application requirements push past what MySQL’s native full-text indexing can provide. Solr maintains a separate index of searchable content, updated asynchronously when records change, and returns ranked results with faceting and highlighting that MySQL cannot produce efficiently.
The schema decision here is understanding where the boundary sits. For an internal logistics application with a search function that filters records by a handful of structured fields, MySQL is sufficient. For a customer portal where users need to search across order history, product descriptions, and attached documents with ranked results, Solr belongs in the architecture from the start.
Adding Solr to an application that was not designed for it is not prohibitively difficult, but it requires building the indexing pipeline, the sync mechanism between MySQL and the Solr index, and the search API layer. Designing for it from the start means the data model reflects what needs to be indexed, the update events are captured cleanly, and the search layer does not need to be retrofitted around application code that was not written with it in mind.
The question to ask early is not “do we need Solr now” but “what does search need to do in this application at scale?” If the answer involves ranked relevance, faceted filtering, or searching across heterogeneous content types, the answer to whether Solr belongs in the initial architecture is almost certainly yes.
What This Means for Evaluating a Development Partner
The schema questions in this article are not advanced topics reserved for senior architects. They are standard practices that any development team with meaningful custom application experience should be able to discuss, defend, and demonstrate in their work. How a team answers questions about soft deletes, multi-tenancy approach, migration discipline, and search architecture tells you whether they are building for the launch or building for the long run. Both kinds of teams exist. Only one of them produces something still serviceable five years later.
Effect’s custom application work runs on MySQL, managed through Doctrine ORM in Symfony, with Apache Solr introduced where search requirements warrant it. The foundation is not novel, but the discipline around it is what determines whether the applications built on it age well. For a full picture of how these decisions fit into the custom development process from discovery through operations, Effect’s approach covers the build from schema through launch.
Talk to Effect About Your Application
If you are evaluating a development partner for a custom application or considering a rebuild of something that has started to show its age, Effect’s team is available for a direct conversation. We work with operations-heavy businesses on the full lifecycle: architecture and data modeling, development, and ongoing support. Get in touch to discuss what you are building.
