Deleting a Column from a Table in Production

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):

1. You still use a database key to map your dimensions.
2. Your new indices on this new Dimension table are properly covering indices when they should be.
3. 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.

I know it is possible, but is it a bad idea? Will my database hate me for it?

I can't speak for your database but I would hate you for it:-)

The legacy column will contain redundant data after the change. This could result in conflicting data if the old column and the new xref table are not maintained consistently with one another. Consider developers who are unfamiliar with the technical debt could logically corrupt the database.

I'm hard pressed to think of a reason why one should not remove the legacy column and relationship. That will also ensure all dependent code was properly changed.