And finally we've arrived at our last entity to store, which is hash tags or tags, and we save this
one for last because as I mentioned in the intro video, it's the hardest, I guess.
Well, it doesn't have to be, but there's a couple of different solutions we could go about, and I
wanted to have a discussion about which is best if there is one answer.
So let's step through three potential structures that we could use.
And remember, the way that this works is that one photo can have ten, 20, 50 hashtags.
I do think there is a limit on Instagram.
I mean, you can keep adding them as many as you want, but only 30, I believe, actually will show
up.
Or it's not that they won't show up, but it's that when you search for a hashtag, your photo and they
show up if you use less than 30 anyways, we're getting off track here, but the idea is you can add
hashtags to a photo, but then you can also go click on a hashtag and see all the associated photos
with it.
So here's one way we could structure it, which might seem maybe the least obvious to you, which would
be to do it like this, where we actually just have our photos table, but we add a column called tags
and it's a string, just a bar chart that just has our tags and we could separate them by the hash sign,
the octo thorp or by spaces or dashes or something.
But we're just storing them together and this could work and places do use this.
I've seen it suggested at least I haven't seen it in practice, but you can imagine how it would work
to add a new tag into something.
You'd basically just take all the other tags and then concatenate in at the end a new tag or at the
beginning, and then you could have an order to figure out which ones were posted first, I guess based
off of that order that they're in.
And it's pretty simple.
If you wanted to find all photos that are tagged with cute, you just basically have to use like.
Where tags like cute.
But then as you can see down here, there are a couple problems.
Let's start with an advantage, which is that it's very easy to implement.
You don't need any other tables, but there are a couple of disadvantages.
One is that there is a limit to how many tags you can have in there, which is the length of whatever
the column is of a bar char, whatever the maximum length is, you can only put so many in there.
The other problem or any other problem, actually, is that you can't store additional information.
Like if we wanted to store the first time a hash tag was used.
If I wanted to be able to see the first person to use the hashtag whatever cat or something, I'm not
storing the time that something was tagged because, you know, on Instagram you can add hash tags after
the fact.
I'm sure they're storing that when they're tagged so that they can track trends as hash tags come and
go.
So it doesn't allow us to store additional information like that.
And then the other thing that's kind of important is that you have to be careful when you're searching
for stuff.
So if I was trying to find the hash tag food and I wanted to see nice food, beautiful food.
So the hash tag is just food.
Well, it will return this, which has sad food.
If I'm just doing like food.
So I'd have to be careful and I need to add spaces maybe to make it easier to separate things out.
Basically, you just have to be careful those ways around it.
We've seen them before how you use search with the like.
But the point is this is an easy solution, but it's not going to be the best and it's not the best
for us right now.
So another solution is to use two tables.
So we have our photos as our photos table currently is now.
But then we have a tags table where we have our tag name in this case cute and then a corresponding
photo ID.
So if this photo with ID of one is tagged cute, we put it there and then a photo with ID three which
is the selfie here is also tags cute.
And then photo ID two is tagged with microwave and three with ego, three with smile, two with gross.
So as you can see, we can have a single photo with multiple tags like here where we have this selfie
ID three is tagged with smile, ego and cute, and we can also have tags that are applied to multiple
photos like cute.
It's applied to the first photo and the third photo.
So this works.
There's unlimited number of tags.
That's an advantage.
We don't have to worry about how many can fit into the string, but a disadvantage is that for one thing,
we are storing these tag names over and over and over and these are strings and that it's maybe not
ideal to store all these different strings and have duplicated data, but it's not a big deal.
The duplication isn't the problem.
Really.
What it comes down to is that this is actually slower than the previous solution when it comes to things
like inserting or updating or deleting.
But then another problem is that it's actually slower than the previous solution.
So in just a moment, I can show you a comparison that I didn't do, but there's a great article that
I found where somebody does a speed test between these the three solutions to figure out when they perform
best.
So this is not one we're going to use either.
But so there's been three issues which maybe you got.
It involves three tables.
So we have our photos table unchanged.
We have a different tags table.
So this tags table is just a tag name and an ID.
And then we have a middle table, a join table called photo tags or tag photos or tags or something,
whatever you want to call it, which is an instance of a hash tag being applied to a photo.
And all that it is is a photo ID and a tag ID, and that's it.
So in this case, photo ID one is my cat is being tagged with cute.
And then the same photo, my cat is being tagged with pets.
And then we have we can keep going.
My meal is being tagged with microwave and my meal is being tagged with groups and so on.
So we have these two columns and that's pretty much it.
The advantage, again, just like the previous one, there's an unlimited number of tags and we can
also add additional information.
So if I wanted to win, a tag is created the first time over here I could store the first time it was
created.
And then over here, every time it's subsequently used, I could store the time it was used or something.
And so I'm sure there's some interesting data you could garner from that or the location of where,
you know, where in the world it was used or something like that at the time of the day, I don't know,
day of the week, whatever.
And we also have less duplication of the tags over here.
But then there are a couple of problems and they really come down to just more work.
So when we're inserting something, if it's a first time a hash tag has been used and it's not in the
database yet, we have to create it and then we have to associate it with a photo using photo tags.
And the same thing when we're updating, we have to.
What?
She just vomited anyway.
And then we also have to worry about orphans.
We should always all be worried about orphans.
But in particular, when we're talking about these tables, when we're deleting something, if we delete
a tag for some reason, which Instagram does.
If there's a problem, if something is being misused or somehow being, I don't know, controversial,
they can be removed.
And so then we just have to make sure when we're removing it from here, we also need to remove it from
all associated photo tags so it can be a bit more complicated.
But basically it comes down to at this the cost of having this nicer structure is that it's just a little
more work.
And just as an end note here, I did mention earlier that there is this great article online, this
guy who does speed tests of different ways of doing tagging and it's written up on the link along with
this video.
If you want to read it, I'll just spend 10 seconds talking about it.
But basically he does some test here to show that depending on one the size of your data, set this
example with a smaller data set.
The second and third approaches I showed you with the multiple tables are actually faster.
But then as we get a larger data set with thousands of tags, it actually changes.
So it's just interesting to note that and to read, but also it talks about how much time it takes to
insert compared to compare it across the three different versions.
And it's just an interesting article, but if you actually look at its conclusion section, which is
probably what most of you care about, where's that?
Is that he actually thinks that it would be best to have if you're working on a huge app to have two
versions.
The the first version I showed you and the third version I showed you.
And basically they perform best at in different situations.
And this is getting into the woods a little bit.
But basically the version that we're going to go with, the third one with three different tables is
faster if you're working with common tags, things that are used often.
But if you're searching or you're trying to do joint with like rare or the wrong word, but uncommon
tags that just aren't used very often, then it's actually slower and it would be faster to do the first
version I showed you where you store them as text.
So this guy is suggesting to use a combination of the two, which we're not going to get into.
But just interesting to note that there isn't one best way and that it really comes down to your needs,
what your app needs, the product needs dictate.
And in all likelihood, as an app like Instagram grows, or especially when it started out to where
it is now, it's schema, it's database has undergone multiple big overhauls and changes, probably
not just the schema itself, but the database, whether it's going from something like MySQL to another
type database or a bigger change going from a relational database like MySQL to a non relational database.
So it's really hard to say what's best, but it's interesting to take a look at somebody who's actually
crunched the numbers and even after doing that, can't necessarily give a definitive answer because
the answer is it depends.