Schema Decisions You Live With
Choosing a real column type, indexing for the queries you will actually run, and treating nullable as a decision.
<column xsi:type="decimal" name="fee_amount" scale="4" precision="20" nullable="false" default="0"/>Declarative schema makes a change easy to write and no easier to undo. Three decisions outlive the release that made them.
Pick the narrowest honest type
text is the lazy answer for a short string. It is stored off-row, it cannot carry a default, and it cannot be indexed without a prefix length. A reference code is varchar with a real length. Money is decimal with precision and scale, matching the scale="4" that core uses for prices, because float will eventually make a total that does not add up. A flag is smallint, which is what core uses in place of boolean.
Index from the queries, not from the columns
Write down the queries the feature needs first. A composite index serves a query whose WHERE uses its leading columns in order; three single-column indexes serve none of them well and slow every insert. An index on a column you only ever read back by primary key is pure cost.
nullable is a decision
nullable="true" means a value can be genuinely unknown, and every piece of code that reads the column then needs a branch. If there is a sensible zero or empty string, declare nullable="false" with a default and delete those branches before they are written.
All of it applies with bin/magento setup:upgrade, after regenerating the whitelist.
Example
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="soundscode_enquiry" resource="default" engine="innodb" comment="Storefront enquiry">
<column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true"
comment="Entity ID"/>
<!-- varchar(32), not text: it is short, indexed, and never null -->
<column xsi:type="varchar" name="reference" nullable="false" length="32" comment="Public reference"/>
<!-- smallint with a default, not nullable: "not archived" is a real state -->
<column xsi:type="smallint" name="is_archived" unsigned="false" nullable="false" default="0"
comment="Is archived"/>
<!-- nullable on purpose: nobody has replied yet is not the same as replied at epoch -->
<column xsi:type="timestamp" name="replied_at" on_update="false" nullable="true"
comment="First reply time"/>
<!-- decimal for money, scale 4 as core uses for prices -->
<column xsi:type="decimal" name="quoted_total" scale="4" precision="20" nullable="true"
comment="Quoted total"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="entity_id"/>
</constraint>
<constraint xsi:type="unique" referenceId="SOUNDSCODE_ENQUIRY_REFERENCE">
<column name="reference"/>
</constraint>
<!-- the admin grid filters on is_archived then sorts by created_at -->
<index referenceId="SOUNDSCODE_ENQUIRY_IS_ARCHIVED_REPLIED_AT" indexType="btree">
<column name="is_archived"/>
<column name="replied_at"/>
</index>
</table>
</schema>When to use it
- Choose between varchar and text for a short code you will filter on.
- Add one index that serves the admin grid instead of three that serve nothing.
- Decide whether a new date column should accept null.
- Store a money amount that still adds up after a thousand orders.
More examples
The same column, declared badly and well
Each line in the first block costs something later. The text reference cannot take the unique constraint it needs. The float total produces sums that are a hundredth of a penny out and cannot be compared for equality. The varchar flag ends up holding '0', 'no', '' and NULL, all meaning the same thing, and every read needs a cast. Narrowing any of them afterwards is an ALTER TABLE that can truncate data.
<!-- the version that gets written in a hurry -->
<column xsi:type="text" name="reference" nullable="true" comment="Reference"/>
<column xsi:type="float" name="quoted_total" nullable="true" comment="Total"/>
<column xsi:type="varchar" name="is_archived" nullable="true" length="255" comment="Archived"/>
<!-- the version you can still live with in two years -->
<column xsi:type="varchar" name="reference" nullable="false" length="32" comment="Public reference"/>
<column xsi:type="decimal" name="quoted_total" scale="4" precision="20" nullable="true"
comment="Quoted total"/>
<column xsi:type="smallint" name="is_archived" unsigned="false" nullable="false" default="0"
comment="Is archived"/>Deriving the index from the query
Write the queries, then the index, in that order. is_archived leads because it is the equality filter; replied_at follows so the sort is satisfied by the same index. A separate index on replied_at alone would add write cost and serve neither query better. A unique constraint is already an index, so do not declare an <index> over the same column as well.
-- the only two queries the admin grid runs
SELECT * FROM soundscode_enquiry
WHERE is_archived = 0
ORDER BY replied_at DESC
LIMIT 20;
SELECT * FROM soundscode_enquiry WHERE reference = 'ENQ-100043';
-- so: one composite btree index, leading column first,
-- and the unique constraint already indexes `reference`.
-- EXPLAIN confirms it before you commit the XML:
EXPLAIN SELECT * FROM soundscode_enquiry WHERE is_archived = 0 ORDER BY replied_at DESC LIMIT 20;
-- key: SOUNDSCODE_ENQUIRY_IS_ARCHIVED_REPLIED_AT rows: 20 Extra: Using whereWhat nullable costs the code that reads it
This is the branch a nullable column buys you, in every consumer, for ever. Here it is worth it: not yet replied is genuinely different from replied at some default time. For is_archived it would not be, which is why that column is nullable="false" with default="0". The two methods show the longhand null check and the ?? shorthand side by side; they behave identically, and you will meet both in other people's code.
<?php
// app/code/Soundscode/Enquiry/Model/EnquiryStatus.php
declare(strict_types=1);
namespace Soundscode\Enquiry\Model;
class EnquiryStatus
{
// replied_at is nullable, so null has to mean something explicit
public function describe(?string $repliedAt, int $isArchived): string
{
if ($isArchived === 1) {
return 'archived';
}
// longhand
if ($repliedAt === null) {
return 'awaiting reply';
}
return 'answered';
}
// the same branch written with the null coalescing operator;
// ?? and the explicit null check are the same thing
public function describeShort(?string $repliedAt, int $isArchived): string
{
return $isArchived === 1
? 'archived'
: (($repliedAt ?? '') === '' ? 'awaiting reply' : 'answered');
}
}
// Output:
// describe(null, 0) => 'awaiting reply'
// describe('2026-03-01 09:00:00', 0) => 'answered'
// describe('2026-03-01 09:00:00', 1) => 'archived'
Discussion