When to Create, When to Modify a Table?

Question

I wanted to know, what should i consider while deciding if i should create a new table or modify an existing table for a sql db. i use both mysql and sqlite.

-Edit- I always thought if i can put a column into a table where it makes sense and can be used by every row then i would always modify it. However at work if its a different 'release' we put it in a different table.

Answer 1

Not sure if I'm understanding your question correctly, but one thing I always try to consider is the impact on existing data.

Taking the case of an application which relies on a database...

When you update the application (including database schema updates), it is important to ensure that any existing, in-use databases will be either backwards compatible with the application, or there is way to migrate and update the existing database.

Answer 2

Generally if the data is in a one-to-one relationship with the existing data in the table and if the table row size is not too large already and if there aren't too many records in the table, then I usually alter the table to accept the new column.

However, suppose I want to add a column with a default value to a table where it doesn't exist. Adding it to the table with 50 million records might not be so speedy a process and it might lock up the table on production when we move the change up. In this case, putting it into a separate table and adding the records to it may work out better. In general, I wouldn't do this unless my testing has shown that adding and populating the column will take an unacceptably long time. I would prefer to keep the record together where possible.

Same thing with the overall record size. SQL server has a byte limit to the number of bytes that can be in a record, it will allow you to create a structure that is potentially larger than that, but it will not alow you to put more than the byte limit into a specific record. Further, less wide tables tend to be faster to access due to how they are stored. Frequently, people will create a table that has a one-to-one relationship (we call them extended tables in our structure) for additional columns that are not as frequnetly used. If the fields from both tables will be frequently used, often they still create two tables but have a view that will pickout all the columns needed.

And of course if the data is in a one to many relationship, you need a related table not just a new column.

Incidentally, you should always do an alter table through a script and the SSMS GUI as it is more efficient and easier to move to prod.

Answer 3

You can modify existing tables, as long as

1. you are keeping the database Normalized
2. you are not breaking code that uses the table

You can create new tables even if 1. and 2. are true for the following reasons:

1. Performance reasons
2. Clarity in your schema logic.