New Identical Table or New Column

Question

New Identical Table or New Column

richarddelcarmen

SSChasing Mays

Points: 625
More actions
August 11, 2010 at 4:28 am

#225922

Good day to everyone,
I have a table that is used for one particular module in a system.
Then a new separate module has a table schema that's identical to the first table.
Is it better to create a new table with the same structure as the first table or
just create a new column that separates data of the two modules?
I'm thinking that it's better to create a new column. That way SQL Server will save database space for the new table and will only maintain the indexes of one table.
Need your thoughts please.
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 1

Is it development environment you are working on ? If yes then it wont make much of difference, unless disk space is constraint.

Say you this table is master table not frequently accessed transaction then adding new column to existing one would be better idea.

Remember the column type determines the space you are going to use.

Cheers

Cheer Satish 🙂

richarddelcarmen SSChasing Mays Points: 625 More actions · Answer 2

Thanks for your reply.

The supposedly new identical table will be frequently updated by the system. With this in mind, is it better to create this new table?

The supposedly new column will have a tinyint data type.

Is the disk space savings between adding a table and adding a column to the first table worth considering?

I hope I'm not asking too many questions. I really want to be on the right track on deciding whether is it better to create a new table or just add a new column to a table to separate the data.

Thanks again.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

I undestand structure will be the same but data will not be the same meaning, the two modules are not going to share any data. Is that correct?

In the affirmative case I will create a new table to serve the new application.

Just my two cents.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

richarddelcarmen SSChasing Mays Points: 625 More actions · Answer 4

Thanks for your reply.

You are right, the two modules will not share data, only the table.

I also believe it is better to create a new table even if the structure is the same.

Thank you very much for all your thoughts and suggestions.

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 5

I'd create a new table and name it something significantly different.

I've done it the other way occasionally (same table and a flag to indicate which data type it is included in the table), but only for low-volume tables.