Deleting a Column from a Table in Production

We have a situation where we need to change the relationship between 2 tables from m:1 to m:n.

So, we need to create a Cross-reference table in between those two tables.

After migrating all existing data from the "child" table into the Cross-reference table, would it be a bad idea to delete the original foreign key column in the child table?

If we leave it there, we basically have technical debt. But I'm not a dba and don't have a good grasp of the implications of deleting a column from a table. (I know it is possible, but is it a bad idea? Will my database hate me for it?)

Thanks

Without knowing all of the structure of your tables I am limited in my advice. However, no, your database will not plot your demise if your remove a column under the following circumstances (by no means exhaustive):

• You still use a database key to map your dimensions.

• Your new indices on this new Dimension table are properly covering indices when they should be.

• You manage this number of indices so as not to overburden Insert/Updates

Your new design has Two Dimension Tables and a Fact table

• This is why it went from m:1 to m:n with a "cross reference" table. We call this another dimension.

Design actually implemented Normalization to achieve this

• By removing a dependency, your team will be better equipped to retrieve Facts that can transform how your data is processed in a more meaningful way.

Note on Dimensions and Facts

• Dimensions for Descriptive Context

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts. With the grain of a fact table firmly in mind, all the possible dimensions can be identified.

Whenever possible, a dimension should be single valued when associated with a given fact row. Dimension tables are sometimes called the “soul” of the data warehouse because they contain the entry points and descriptive labels that enable the DW/BI system to be leveraged for business analysis. A disproportionate amount of effort is put into the data governance and development of dimension tables because they are the drivers of the user’s BI experience.

• Facts for Measurements

Facts are the measurements that result from a business process event and are almost always numeric. A single fact table row has a one-to-one relationship to a measurement event as described by the fact table’s grain. Thus a fact table corresponds to a physical observable event, and not to the demands of a particular report. Within a fact table, only facts consistent with the declared grain are allowed. For example, in a retail sales transaction, the quantity of a product sold and its extended price are good facts, whereas the store manager’s salary is disallowed.

Kimball Dimensional Modeling Techniques

My suggestion is the design team should know that enforcing rules in the database is best, unless it hurts performance. I do not know the size or quantification of your DDL statements to fully answer this, though.

But rest assured this should be a positive change to your system as now SQL Server will not have to go through all that extra data to retrieve what actually mattered.

StackExchange Database Administrators Q#185662, answer score: 5