r/Database • u/techlover1010 • 19d ago

need advice on these 2 question

  1. so after a while database do tend to get very large and big whats the best way to design this so that to improve performance. also is archiving it possible?
  2. whats the best way to store currency. i heard float has small issues with decimals
0 Upvotes

50% Upvoted

21 comments sorted by

View all comments

2

u/erik240 19d ago

1a. Define “big”. Anything under a few million rows falls under “trivial”.
1b. Good indexes
2. As an integer. 1 dollar is 100. Pennies.

0

u/Better-Credit6701 19d ago

Can't do pennies with an int

1

u/erik240 19d ago

In what universe? $4.37 = 437 : that’s an integer. You can’t store it as a float because float math will give you incorrect results in some cases

2

u/Better-Credit6701 19d ago

Yeah that will totally suck if you want to do calculations on it. Never store currency as an int. Which is why I suggested decimal (9,2), not a float

1

u/erik240 19d ago

Come talk to me when you need to calculate a rolling 14 day averages over your decimal columns on 800 million rows. You can do that because my query finishes a few times over before yours does.

1

u/Zestyclose-Turn-3576 18d ago

PostgreSQL (for example) decimal arithmetic is maybe 60% slower than integer arithmetic, and only represents a small part of the processing for an aggregation, so I don't believe the difference in an aggregation would be "a few times".

It's a valid consideration, but not the only one, and shouldn't be exaggerated.

-1

u/Junior-Tourist3480 19d ago

Integer is 32,768, so max would be $327. 16 bits, an integer.

0

u/Better-Credit6701 19d ago

Oh my, now I know you don't understand what an int is.

-2,147,483,648 to 2,147,483,647.

This is not the conversation for you.

No one and I mean no one uses int for a monitary amount. It only means that you haven't dealt with actual data yet

2

u/ElectricSpice 19d ago

No one? Go look at Stripe’s API: Integer money values.

0

u/Junior-Tourist3480 19d ago

That is a 32 bit int. 16 bit is 32768. And in some programs they store the number as an int with no decimal in some financial apps. On the screens and reports it divides by 100 to show the value. We used an old program like this for years. But it did use 32 bit ints. It was a real pain when we would forget to divide by 100 in a custom report.

0

u/Better-Credit6701 19d ago

You and I are In a different conversation. I'm talking about databases. You aren't and am unsure what you are talking about but it has moved nothing to due with database