Consolidating Similar Tables or Bad Idea? (1 Viewer)

Anthony.DG

Registered User.
Local time
Today, 11:16
Joined
Oct 18, 2019
Messages
27
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.
    3KingsOfMaterial.JPG
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).
OMD.JPG


  • 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?
 

Attachments

  • 3KingsOfMaterial.JPG
    3KingsOfMaterial.JPG
    63.5 KB · Views: 190

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:16
Joined
Feb 19, 2002
Messages
42,971
Having tables with similar column names isn't the same as tables holding the same data perhaps at different points in time. For example, if your business sells customized products, you might have a proposal and an order. You might initially think of these as separate since not all proposals become orders. But these are actually candidates for merging since all you would need to do would be to add a stage code so you could distinguish proposals from orders and to turn a proposal into an order, you just change the stage or status or whatever you want to call it. If invoicing is separate, you would use a reference to the order table rather than duplicate the data.

I sometimes combine customers and vendors into a single table particularly if vendors can be customers Or teachers, parents, students might be combined, especially for institutions of higher learning where one person could have all three rolls.

So, it is not the column names, it is whether or not the tables contain the same data at a different point in time.

PS - avoid embedded spaces and special characters and consistency is your friend. If you like using _FK as the suffix for foreign keys, fine but don't vary from that standard or you have no standards.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 18:16
Joined
Feb 19, 2013
Messages
16,553
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

Immoderate Moderator
Staff member
Local time
Today, 13:16
Joined
Feb 28, 2001
Messages
26,999
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.
 

Users who are viewing this thread

Top Bottom