Transaction Data Base Table Design

Transact-SQL
1

Hi Friends,

We are Designing Database for a project. We have

  1. Customer Master Table
  2. Item master Table
    a) Item Sub Master

Each Customer will have min of 3000 Items
Each item will have min of 30 Sub Items.

i.e, For each customer we 9000 rows in the transaction table.

Suppose if we have 500 Customers 45,00,000 rows

We thought of keeping each customer data in text file (Json Format)

Kindly suggest what is this the best way to handle the transaction DB. Frequent Update will be there for the table.

2

why would you keep customers in JSON? Probably not the best idea.

You haven't said what the transaction table is used for or what it looks like.

3

I would go one step further and suggest that if you plan to do any type of queries on the data, storing it as JSON is probably the worst possible choice. The reason for that is that T-SQL has no support for JSON (at least until SQL 2016, and even then, from what I know, it is going to be limited support, and it is not going to be very efficient).

A table with 4,500,000 rows is really not very large in terms of what SQL Server can handle. My recommendation would be that you parse the Json data during the import process and store them in relational tables.

All of what I said is theoretical, and generalities. What would work best for you in your specific situation depends on your data, and how you plan to use it.