How to factor out similarities in history and portfolio tables?

Question

I did the history problem by making a separate table with all of the values that I wanted to show to the user, all of which were the same as that of my portfolio page but instead would not be deleted when stocks were sold. So I have a couple questions about this:

1) Since I never need to manipulate data afterwords for history did I need to create a whole new table at all? Maybe this is a dumb question, but if I could somehow store the information in portfolio I could (maybe?) use it for buy/sell and history combined?

2) If I do need the extra table, can I join the tables like Malan shows in his lecture? In the example in the lecture, we wanted to not have to repeat similar zip codes, so we stored the zip codes in a separate table and later joined the users table with the zip code table where the users table had the foreign "zipcode_id" which meant that a zip code could be represented as an integer in the users table. But how could I limit redundancies for this problem considering that my history and users table have identical fields?

Answer 1

All very good questions. You're thinking about what redundancies can be eliminated. But then, you need to think further. What's more important, conserving storage or making queries more efficient?

First, look at the case of the zip code table. Zip codes are essentially unique and constant. Info related to cities and zip codes rarely change. Linking back to that table to get the data related to a zip code is probably more efficient than repeatedly storing the same data in other tables.

Now, think about the history and the portfolio tables. What data is actually duplicated? As I see it, only the symbol and stock name remain constant. After that, the number of shares, quantity, price, type of sale(buy/sell), time of transaction, etc., are all likely to be different. Each row in the history table will document a single transaction and will not change. On the other hand, the portfolio table will constantly update, add and remove records. It will keep a running total of shares of each given stock. This data can easily be updated on a transaction by transaction basis, but would be inefficient if it had to go back to the history table every time and recalculate the number of shares of a given stock that someone has. Further, what happens when part of that history gets archived, or worse yet, lost or corrupted?

It's far better in this case to replicate the common data (userid/symbol/stock name) than to try to minimize the storage of it across tables. This is especially true when the duplicated data may only be one or two columns. It may even be useful to have them duplicated in order to create unique indexes.

Finally, as you think about these questions, start thinking about databases that have thousands of users and millions of transactions overall, and processes thousands of transactions in minutes or seconds.

You're welcome to disagree. This is just the advice of a database engineer, I could be wrong. ;-)

If this answers your questions, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)