Designing a data model that lasts for years
Start from the entities
What the things in the world are — customer, order, item, payment — and the relationships between them. Names that fit the language the business team speaks save countless misunderstandings.
Don't flatten many-to-many relationships into text fields. “Tags separated by commas” is a debt paid painfully the first time you need to filter by them.
Distinguish a fact from a state
A current state changes. A fact — happened. An order has a state; a payment is an event that occurred. Systems that lose the history can't answer basic questions like “what was the price on that day.”
So: keep event records alongside state records, especially for anything touching money and permissions.
Fields that are always worth it
Creation and update time, who performed it, a version or stamp to detect conflicts, and a soft delete instead of a hard one in places where recovery may be needed.
Going deeper
When a model needs to change, prefer addition over deletion, and run the change as a staged migration. And document model decisions briefly — why there are two tables and not one — because in a year nobody will remember, and the temptation to simplify something that was deliberate is great.