Consolidating Similar Tables or Bad Idea?

[Anthony.DG]

I have a database that has these different tables that stores records pertaining to Material.

• tblTicketing - Materials put on a truck weighed
• tblInvoiceMatDetail - Materials selected for estimates
• tblDemoDeposit
  a. Bank of construction site demo material in our yard that we will later recycle
  b. Bank of material that’s purchase from other quarries and deposited in our yard.
  
  

I was thinking about consolidating these tables to one. I created a table that would store what was needed any of those records. I added check value fields to separate them (yes/no).

• ToInvoicing - for estimates and billing
• ToTicket – for weight tickets
• ToBank – for stored material on site

To have them filter where they came from and where they go, I’ll just set the form with the corresponding field with a default form value of YES.

So I figure this might be the way to go instead of having multi tables that have the very similar data. It could form other problems Im not aware of. I know I'll have to adjust all relevant queries, vba and other whatnots, I dont mind that. What do you guys think?

 

[CJ_London]

looks like a candidate for consolidation - But I would include a field to indicate what type of 'transaction' each record relates to - other thing to consider is whether all quantities should be positive, regardless of the direction in/out of the yard or positive for in and negative for out (or the other way if you prefer). Matters if you are using the data for stock take purposes

Which way really comes down to how you app works - if predominately manual user input, probably better to keep all positive, if input is from data obtained from another app, perhaps use that form to avoid confusion if you have to do a reconciliation

If all positive you can include a small calculation to make it negative for specific types of transaction when summing.

And if used for this purpose, consider other types of transactions - corrections to document values, adjustments after stock take etc.

 

[The_Doc_Man]

Here is the acid test: What would you do with the tables when separate and what would you do differently if they were consolidated? Database table design is all about relevant content but it includes how you intend to USE the data. Consolidation works if it makes sense to use the same tables in multiple points along the way in your business flow using the same primary key to keep things separate and identifiable..

Another test would be if you would have to use the same primary key in the table for things that lose or change their "identity" during the process. If so, then merging the tables might be of questionable value.