1

I'm really interested in knowing what the best way to structure relational database tables in the following scenario is.

Let's say the table is recording cryptocurrency prices. There are four important aspects:

1. An ID
2. The date and time
3. What cryptocurrency it is
4. The price

I'm thinking of two possible ways to structure the resulting table, either:

ID    Date                  Crypto        Price
1     2018-01-01 12:00:00   BTC           100000
2     2018-01-01 12:00:00   ETH           1234

Or

ID    Date                  BTC_Price    ETH_Price....
1     2018-01-01 12:00:00   1000000      1234

Is there a "best practice" to structure these kind of tables? One consideration that I have is I might want to add more types of currencies in this case. I can do this with the ALTER command, but in that context the first layout makes more logical sense to me.

What are your thoughts?

Improve this question
2
  • "What are your thoughts" is not an on-topic question. Time to read a book on information modeling, the relational model & database design.
    – philipxy
    Feb 1, 2018 at 22:07
  • You're mixing data and metadata. If your design requires you to add columns for new data, it's wrong.
    – Colin 't Hart
    Feb 2, 2018 at 16:07

2 Answers 2

Reset to default
2

Your first example is considerably better than your second. It is a standard rule to not use repeating groups of fields and to break fields down to their smallest meaningful value. BTC_Price and ETH_Price are not broken down to their smallest meaningful value; price and crypto. In the first example you separated the crypto from price. This is the correct thing to do. Joe is also correct in that you should have another table to hold the different kinds of cryptocurrencies with a auto incrementing primary key. This is often referred to as a lookup table.

Have a look at database normalization and learn about what are called "normal forms". Your first table conforms with what is called "3rd normal form" and your second table conforms with I believe "2nd normal form". Typically the higher the normal form the better but 3rd normal form is considered the standard that you should attempt to achieve at minimum.

Improve this answer
1
  • 1
    No functional dependencies were given, but IMO in table 1: ID -> Date, Crypto, Price and Date, Crypto -> ID, Price , while in table 2: ID -> Date, BTC_Price, ETH_Price and Date -> ID, BTC_Price, ETH_Price. Both are in 3NF at least. In addition, your use of "repeating groups" isn't consistent with the original meaning and isn't related to normalization - see Normalization: What does “repeating groups” mean?.
    – reaanb
    Jan 30, 2018 at 18:50
1

Option 1 is the normalized way to structure the table with one addition. There should be a table of types with an id stored in the main table as a foreign key. That way when you add new types there is no need to change the database or reports.

Using different reporting methods you can create either view you had in your question.

https://en.wikipedia.org/wiki/Database_normalization

Improve this answer
2
  • @reaanb I dont understand what you mean. One column is the type of currency another column is the value of a single unit in some currency USD for example. Based on everything I learned on the topic is that option 1 is more 'normalized'.
    – Joe
    Jan 30, 2018 at 19:20
  • 1
    I assumed that the Price column contained values of different units - thanks for clarifying that. I still say that option 1 isn't more normalized - see my comment on dutsnekcirf's answer.
    – reaanb
    Jan 30, 2018 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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