CODEWITHSUNDEEP
sqldatabasedata-structures
Mark O'Grady
Mark O'Grady

Reputation: 1184

Different Types saved values to SQL Database

I am currently writing an application which will have a lot of transaction. Each transaction will have a value although the value can be an int, bit, short string, large string etc...

I want to try to keep processing and storage to a minimum as I would like to run this in the cloud. Should I have lot of different fields on the transaction eg.

TransactionLine.valueint
TransactionLine.valuestring
TransactionLine.valuedecimal
TransactionLine.valuebool

or should I have separate tables for each value transaction value type.

TransactionLine - Table
---------------
TransactionLine.ValueId


ValueInt -Table
-------
ValueInt.ValueId
ValueInt.Value

ValueString - Table
-------
ValueString.ValueId
ValueString.Value

Upvotes: 0

Views: 102

Answers (2)

Wim
Wim

Reputation: 1096

You could store key-value pairs in the database. The only data type that can store any other data type is a VARCHAR(MAX) or a BLOB. That means that all data must be converted to a string before it can be stored. That conversion will take processing time.

In the opposite direction, when you want to do a SUM or a MAX or an AVG , ... of numeric data you will first have to convert the string back to its real data type. That conversion too will take processing time.

Databases are read a lot more than written to. The conversion nightmare will get your system on its knees. There has been a lot of debate on this topic. The high cost of conversions is the killer.

There are systems that store the whole database in one single table. But in those cases the whole system is build with one clear goal: to support that system in an efficient way in a fast compiled programming language, like C(++, #), not in a relational database language like SQL.

I don't have the idea I fully understand what you really want. If you only want to store the transactions, this may be a worth trying. But why do you want to store them one field at a time? Data is stored in groups in records. And the data type of each and every column in a record is known at the creation time of the table.

Upvotes: 1

Jasonw
Jasonw

Reputation: 5064

You should really look into cassandra. When you say a lot of transactions, do you mean millions of records? For cassandra, handling millions of records is a norm. You will have a column family (in rdbms, table is similiar to column family) store many rows, and for each row, you do not need to predefined a column. It can be define on demand, thus reducing the storage dramatically especially if you are dealing with a lot of records.

You do not need to worry if the data is of data type int, string, decimal or bool because default datatype for column value is in BytesType. There are other data types which you can predefined too in the the column family column metadata if you want to. Since you are starting to write an application, I will suggest you spend sometime to read into cassandra and how it would help you in your situation.

Upvotes: 1

Related Questions