SQL Schema Change; Modification of Existing Table vs Creation of New

Question

We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to store associated data.

Is it better to extend an existing table or create a child table with a FK reference to the existing table? I assume creating a child table with a FK to the existing table is the better option because the existing database schema remains stable.

• We're committed to a relational database so an advice on choosing NoSQL is not what we're looking for.
• Keep in mind that the changes aren't expected to be frequent.

Answer 1

If your changes are not frequent and your parent table does not have a lot of data, then you could just update your schema on that table.

However, potential downsides are changes in the schema could propagate to changes in stored procedures and views. This could be because of adding columns or removing columns.

(Of course, any change made in a database schema, will have some ripple effects that must be coped with.)

I like the parent table to be as stabile as possible. Having a related table or tables to provide new requirements is a good general rule according to how I look at schemas.

But it is definitely an opinion-based choice that will depend on what you are most comfortable with doing.