CODEWITHSUNDEEP
performancedatabase-design
Titi Wangsa Bin Damhore
Titi Wangsa Bin Damhore

Reputation: 888

Database design: Same table structure but different table

My latest project deals with a lot of "staging" data. Like when a customer registers, the data is stored in "customer_temp" table, and when he is verified, the data is moved to "customer" table.

Before I start shooting e-mails, go on a rampage on how I think this is wrong and you should just put a flag on the row, there is always a chance that I'm the idiot. Can anybody explain to me why this is desirable? Creating 2 tables with the same structure, populating a table (table 1), then moving the whole row to a different table (table 2) when certain events occur.

I can understand if table 2 will store archival, non seldom used data.

But I can't understand if table 2 stores live data that can changes constantly.

To recap: Can anyone explain how wrong (or right) this seemingly counter-productive approach is?

Upvotes: 0

Views: 418

Answers (4)

Walter Mitty
Walter Mitty

Reputation: 18940

I would have used a single table design, as you suggest. But I only know what you posted about the case. Before deciding that the designer was an idiot, I would want to know what other consequences, intended or unintended, may have followed from the two table design.

For, example, it may reduce contention between processes that are storing new potential customers and processes accessing the existing customer base. Or it may permit certain columns to be constrained to be not null in the customer table that are permitted to be null in the potential customer table. Or it may permit write access to the customer table to be tightly controlled, and unavailable to operations that originate from the web.

Or the original designer may simply not have seen the benefits you and I see in a single table design.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Any time I see a permenant table names "customer_temp" I see a red flag. This typically means that someone was working through a problem as they were going along and didn't think ahead about it.

As for the structure you describe there are some advantages. For example the tables could be indexed differently or placed on different File locations for performance.

But typically these advantages aren't worth the cost cost of keeping the structures in synch for changes (adding a column to different tables searching for two sets of dependencies etc. )

If you really need them to be treated differently then its better to handle that by adding a layer of abstraction with a view rather than creating two separate models.

Upvotes: 0

Daniel Fath
Daniel Fath

Reputation: 18069

There seems to be several explanations about why would you want "customer_temp".

  1. As you noted would be for archival purposes. To allow analyzing data but in that case the historical data should be aggregated according to some interesting query. However it using live data does not sound plausible

  2. As oded noted, there could be a certain business logic that differentiates between customer and potential customer.

  3. Or it could be a security feature which requires logging all attempts to register a customer in addition to storing approved customers.

Upvotes: 0

Oded
Oded

Reputation: 499002

If there is a significant difference between a "customer" and a "potential customer" in the business logic, separating them out in the database can make sense (you don't need to always remember to query by the flag, for example). In particular if the data stored for the two may diverge in the future.

It makes reporting somewhat easier and reduces the chances of treating both types of entities as the same one.

As you say, however, this does look redundant and would probably not be the way most people design the database.

Upvotes: 2

Related Questions