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 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 you know 10 20 50 hash tags.
I do think there is a limit on Instagram.
I mean you can keep adding them as many as you want but only 38 believe actually will show up or it's
not that they won't show up but it's that when you search for a hash tag your photo will not show up.
If you use less than 30.
Anyway as for getting off track here but the idea is you can add hash tags to a photo but then you can
also you know go click on a hash tag 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 Savarkar that just has our tags and we can separate them by the hash sign the Octa Thorpe
or by spaces or dashes or something but we're just storing them together.
And this could work in places to use this.
I've seen it suggested at least I haven't seen it in practice but you can imagine how would work you
need 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 tack.
Or at the beginning and then you could have an order to you know figure out which ones were posted first
I guess based off of that order that they're in and it's pretty simple.
You know if you want to find all photos that are tagged with cute you just basically have to use the
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's a limit to how many tags you can have in there which is the length of you know 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
you know if we want to store the first time a hash tag was used.
If I want to be able to see the first person to use the hash tag whatever cat or something I'm not storing
the time that something was tag because you know on Instagram you can add hash tags after the fact.
Sure they're storing that when they're tagged so that they can track trend 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 wanted to see nice food beautiful foods with the hash
tags just food well it will return this which has said 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 have to be careful in those ways around it.
We've seen them before how you used search with 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.
So then we have a tag's table where we have our tag name.
In this case cute and then a corresponding photo ID.
So you know if this photo with idea of one is tag's cute we put it there.
And then a photo with ID 3 which is this selfie here.
It's also tagged cute and then photo-ID too is tagged with a microwave and three with ego through smile
too with gross.
So as you can see we can have a single photo with multiple tags like here where we have this selfie
D-3 is tagged with the 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.
You know 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 you know these tagged 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.
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 three solutions to figure out when they perform
best.
So this is not one we're going to use either but solution 3 is which maybe you've got it involves three
tables.
So we have our photos table unchanged.
We have a different tag's table so this tax 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 tagging 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 a photo ID and tag ID and that's it.
So in this case you know photo I.D. one is my cat is being tagged with cute and then the same photo
My cat is being tagged with pet.
And then we have you know we can keep going.
My meal is being tagged with microwave and my meal is being tagged with gross and so on.
So we have these two columns.
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 you know when 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 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 what 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 it 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 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 right if we delete a tag for some reason which Instagram does you know
if there's a problem something is being misused her somehow being you know 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 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 test of different ways of doing tagging and it's written up include 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 tests here to show that depending on the size of your data set this example
with a smaller data set.
The second and third approaches I showed you with multiple tables are actually faster but then we get
a larger dataset with thousands of tags it actually changes.
So it's just interesting to note that and to read.
But also he talks about that you know how much time it takes to insert compared to you know compare
to cross the three different versions.
And it's just an interesting article but if you actually look at his conclusion section which is probably
what most of you care about where said 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 first version I sure do.
And the third version I showed you.
And basically they are performed best 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 you know are used often but if you're searching or you're
trying to do joints with rare rare is the wrong word but uncommon tanks it 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 know that there isn't one best way and that it really comes down to your needs and what
your app needs the product needs dictate.
And in all likelihood as an athlete Instagram grows especially when it started out to where it is now
its schema its database has undergone multiple big overhauls and changes probably not just the schema
itself but the database.
Whether its going from something like my ask you all to another type of database or a bigger change
going from a relational database like my ice cube to a non-relational database.
So its really hard to say you know 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 you know a definitive
answer because the answer is it depends.