Create a History Table in Postgres with Joins

[00:00] Let's take a look at a real-world use case for joins. We can select off from a bids table. This bid table is backed by a list of histories. These are audits for the table. We can get to those audits if we inter-join on the bid histories. On the bids.ID equals the bid histories.bidID.

[00:30] We can also find out what user placed that bid on u.ID equals bid histories.history userID. We can order by a field that this will have column histories started at. We can take a look at what this looks like. It's a little hard to see, so let's start breaking it down.

[00:53] Let's say we want to get the bid's object name -- this is what was bidded on -- the user's name. We want to get the bid histories sense. How much do they bid? We want to get their history started at, history ended at. That should be enough for us.

[01:16] We can see here that the first thing that was bid on was this poster, First Dog bid of five dollars. We can see at what time he bid that, and what time it ended. Then Skitter bid seven dollars, what that time bid started and ended.

[01:38] We can continue to see the history of how this bidding war played out, ultimately resulting in these two values that we saw in the previous table. We see that they have null ended at columns, indicating that they're the present bid.

[01:53] This is how a table like this tends to work. Under the hood, there would be some backing structure that says in order for this transaction to succeed, we'll have to insert a record into the history table as well. This is a really good use case for joins, is being able to look at an audit trail and figure out exactly when your data changed, and who changed that data.