locked
Database Design To Store A Field That Can Be A Sub-Table On Its Own, For Each Row RRS feed

  • Question

  • Hello!

    My question is regarding database design/architecture, but I'll use a familiar example to explain it.

    Suppose there is a database for banking. This database has a table called "Customers" which stores ID, Name, Address, etc., Now each of these customers can have their own sub-table for Transactions, with columns like Transaction ID, Date, Time, Amount, etc.

    My question is, what is the efficient way to store this Transactions table. Should I create a new table in the database, add a field User ID, and insert transactions of ALL customers in this table. Or should I add a text field "Transactions" to the table Customers and store all transactions of each user in JSON format? I'd like to know how this is done in professional industries.

    Thank you!


    - Prateek Jain

    Thursday, December 1, 2016 9:19 AM

Answers

  • What are your requirements?

    Lookup the difference between a relational database and an object store:

    Without storing your transactions in its own relations (tables) the RDBMS ensures ACID. When you only store them as objects, your application must ensure this. So any error in your code can render the data in an object store worthless. Any kind of object store imho can only assure BASE.

    So it just depends on the requirements.

    Or as a real world example: Consider you have to import data from different sources. These sources have different structural configurations. You normally don't need to ask the database to look for configurations where a special options is set as regular operation. Here makes an object store perfectly sense. Cause you need only one head table and you don't need to add tables when you'll at new sources. You just serialize your configuration from/into your applications object graph. And when something gets wrong, you've only lost a "simple" configuration.

    Transactions on the other hand needs normally a more strict handling, cause when something gets wrong, you cannot longer display a correct transaction history. Consider a bank losing some transaction records. Then the money is also lost.

    Thursday, December 1, 2016 9:41 AM
    Answerer

All replies

  • What are your requirements?

    Lookup the difference between a relational database and an object store:

    Without storing your transactions in its own relations (tables) the RDBMS ensures ACID. When you only store them as objects, your application must ensure this. So any error in your code can render the data in an object store worthless. Any kind of object store imho can only assure BASE.

    So it just depends on the requirements.

    Or as a real world example: Consider you have to import data from different sources. These sources have different structural configurations. You normally don't need to ask the database to look for configurations where a special options is set as regular operation. Here makes an object store perfectly sense. Cause you need only one head table and you don't need to add tables when you'll at new sources. You just serialize your configuration from/into your applications object graph. And when something gets wrong, you've only lost a "simple" configuration.

    Transactions on the other hand needs normally a more strict handling, cause when something gets wrong, you cannot longer display a correct transaction history. Consider a bank losing some transaction records. Then the money is also lost.

    Thursday, December 1, 2016 9:41 AM
    Answerer
  • and store all transactions of each user in JSON format?

    Explain why this is a concern at this point?

    My question is, what is the efficient way to store this Transactions table.

    There are no absolutes. No one can answer questions like this because no one but you know how you intend to measure "efficient". Also, no one knows exactly what you are modeling and how you intend to use the information you intend to store. Perhaps even you don't know these things yet either. But since you seem to have no particular direction, I suggest you review the schemas for the standard sample databases used by MS - Adventureworks (older) and World Wide Importers (newest).

    MS sample databases

    Thursday, December 1, 2016 6:48 PM
  • I guess having a separate transaction table for ALL the customers will be more "efficient". Since I don't have to fetch and parse entire JSON string (which will consume more resources like network bandwidth and server RAM) for operations that don't require the entire data (like only adding up total amount...here I don't need dates of each transaction or the MEMO). So JSON or object store is better only in the cases when I need the data in it's entirety for all operations. Thanks!

    - Prateek Jain

    Tuesday, December 13, 2016 6:57 AM