-1

Started designing databases very recently. I am wondering if I chose the right data types. For example, I was told to use integers for $ amounts to avoid troubles. Also for tax rates I put decimals, I assume I will have to convert it back to integer when calculated.

Thanks!

Database design:

Database design

New contributor
Philippe G is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 3
    Please edit the question and tell us which table and column in particular you're concerned about. What is it supposed to model from the real world and what are the demands and limitations the real world applies to it? Also who told you about the integers and what "troubles" do they mean? And please preferably post tables as CREATE statements as text, not images. – sticky bit 2 days ago
  • There is a school of thought like that, but I'd be happy with decimals myself – Strawberry yesterday
  • 1
    It's worth noting there's a huge difference between DECIMAL, as in fixed-place, and FLOAT, as in floating-point. The former is predictable. The latter is a whole lot of hurt if you're not prepared for it. – tadman yesterday
1

Integers work great for integers, but not decimals. You can store pennies (or a thousandth of a penny) in an integer and then convert to to dollars when you display it in much the same way that we store dates as "seconds since 1st Jan 1970" but display them differently.

the trick there is to ensure your conversion always applies, and that's not so easy when using a common datatype like Integer.

DBs all have a decimal datatype that is fixed precision (NOT float or double) so use that and everyone will be less confused as to what it going on with a currency entry.

| improve this answer | |
  • If the fixed-place datatype is matched with a fixed-place equivalent in application code you'll be fine. If not, watch out! – tadman yesterday

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