-1

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.

0

2 Answers 2

1

For clarification, in a relational database tables are just tables, not subtables. They may be dependent on other tables (called child tables).

You want to create a table TRANSACTION and have CUSTOMER as a FK to that table, along with other data that are relevant to a transaction.

I'm making some assumptions here based on your table names. But putting data in a JSON format inside a column in a table will make aggregations much slower. Plus the other reason one might consider using JSON, such as highly variable data requirements, likely does not apply in this example.

The other assumption I'm making in this answer is that the you will have application requirements to aggregate, calculate and query data across customers. A JSON solution inside a relational table won't perform well there, at all.

1
  • By subtable, I meant that that field is worthy of being a table on its own. Yes you are right. Having a separate transaction table for ALL the customers will be more "efficient". Since I won'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 is better only in the cases when I need the data in it's entirety for all operations. Dec 13, 2016 at 7:01
1

The standard approach is to use a Transaction table for all customers.

Storing data in row in json format would mean that the data is only readable by your application, not by other client software that accesses the database. It may also invalidate built in protections that databases have relating to the ACID properties of transactions.

A properly designed Transaction table should have performance no worse than storing transactions in each Customer row.

1
  • You mean a single transaction table for ALL the customers right? I think the other client softwares' developers could be made aware about the json format, right?. Or probably they would also be developed by same company...so they'd know the structure. Dec 1, 2016 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.