Future Proofing SQL table?

I am considerin开发者_运维百科g creating a MySQL Data Base for a non-mission critical project. I was thinking about a scenario when, in case I had to insert another column in a table, how can I achieve that without compromising data integrity of the pre-existing columns as well as specify the default values of those additional columns? I'd like the answer for both SQL and Java Persistence API (JPA).

In addition, I'd also like to know how to add a table within a cell of a parent table.

It's easy:

ALTER TABLE mytable
ADD COLUMN newcolumn VARCHAR(20)

You can give it a default value, you can use the AFTER xyz clause to add it after the "xyz" column (or leave it off to just add the column to the end).

There's nothing special about any of this, any RDBMS will let you add (and remove) columns to a database on-the-fly.

As stated, you can simply add a column to a table using the syntax of your RDBMS of choice.

If you don't want to change your code at that point, all you can do is provide a sensible default (null could be a sensible default, too). That way your application can still insert rows without any issue. At a later time you can map your column and start using it in your application.

To be fair, It's pretty common to release a dbschema update together with a new version of the application.

One thing that may have in impact on your code is if the code refers to position column instead of keyword column. If you expect a given firstname to be at position 5, instead of referring to it with the keyword firstname, you may want to add the new column at the end of your table.

The addition of a column should have no effect on pre-existing data, unless you do something that links the columns, such as creating a multi-column index, unique key, or something similar. But if there are no such linkages between the new & old columns, add as many columns as you like. Go crazy!