locked
Need help designing these 4 types of data (one table vs 4 tables) RRS feed

  • Question

  • I have three type of data in my system: Invoices, Payments, Refunds, Transfers. 

    I was wondering what's considered a better design, to combine these into one table named "Transactions" and just have a TransactionType column to indicate which type it is.  Or is it better design to Split them all out into 4 seperate tables?

    All 4 types of data have some similar fields (which leads me to the one table design), yet they each have some different/unique fields (which leads me to 4 seperate table design.) 

    Not sure if it would be helpful to see the actual fields, but they are probably what you would imagine for these basic types of data. 
    Invoice: A request for payment from a person
    Receipt: A payment by a person
    Refunds: A refund of a payment.
    Transfers: Transfer a Receipt to a different person.

     

    Thursday, November 4, 2010 8:13 PM

Answers

All replies

  • Try doing a search on "subcategory relationship" and see if that helps.  You might want to give a look at and follow this thread from earlier today and see if you can see some common ground between your problem and this other problem:

    http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/1d63f58c-471d-4608-9e97-0818cc4a0fe8

    • Marked as answer by ICJIS Team Friday, November 5, 2010 7:10 PM
    Thursday, November 4, 2010 10:43 PM
  • It is certainly a good design consideration to use a transaction type code. Not knowing the columns (attributes) of the tables (entities) makes it difficult to say one way or the other....or even that you might consider additional tables. :) Since there are columns that are not the same it is possible you would be breaking a normalization rule by combining the tables, but denormalization has it its place in the real world.

    Would you be using a natural key or a surrogate key? What makes a row unique in the four tables?

    Combining the tables, you need to consider how many rows, growth, query performance, number of users...performance. For example, it would seem most transactions would be against Invoice and Receipt. If the tables were separate, with future and unexpected growth you would be able to consider distributing the tables on multiple drives. Using the transaction type code (single table) you would consider partitioning.

     

     

    Friday, November 26, 2010 6:22 AM