Database Design 22 - Look up Table

Start your software dev career - calcur.tech/de... 💯 FREE Courses (100+ hours) - calcur.tech/al...
🐍 Python Course - calcur.tech/py...
✅ Data Structures & Algorithms - calcur.tech/ds...
~~~~~~~~~~~~~~~ CONNECT ~~~~~~~~~~~~~~~
✉️ Newsletter - calcur.tech/ne...
📸 Instagram - / calebcurry
🐦 Twitter - / calebcurry
🔗 LinkedIn - / calebcurry
▶️ Subscribe - calcur.tech/sub...
👨🏻‍🎓 Courses - www.codebreakt...
~~~~~~~~~~~~~~ SUPPORT ME ~~~~~~~~~~~~~~
↪ My Amazon Store - www.amazon.com...
🅿 Patreon - calcur.tech/pat...
🅖 GitHub Sponsors - github.com/spo...
Ⓟ Paypal - paypal.me/calcur
🅑 Bitcoin - 3HnF1SWTzo1dCU7RwFLhgk7SYiVfV37Pbq
🅔 Eth - 0x350139af84b60d075a3a0379716040b63f6D3853
📈 Buy Bitcoin - calcur.tech/cr...
Reserve the Ruby Steel crypto rewards card and get a $25 bonus (use affiliate code "Caleb") - calcur.tech/cr...

[@cjcjonesit]

Great video, I hope after 5 years that he can buy an eraser. lol

[@mohammedfalih8713]

there is a myth says before years he was spitting on the board 😂

[@abdirahmanmohamod3106]

Stop Bullying You are too old to bully

[@alcatelkey]

There seems to be a problem with the video from 14:55 - 15:35. Other wise, good information.

[@ProduccionesLukaz]

that's where he tells us the secret of the universe

[@thetasworld]

@@ProduccionesLukaz it's 42 but you probably shouldn't add that on a lookup table...

[@utilizator1701]

When I have designed my database, I had an entity with a group of attributes with repetitive data. In order to reduce the space, I have moved that group of attributes into a new entity. I have never known if this is a correct way to reduce the space of the database, but now I have the confirmation that I have done well. Thank you, Caleb!

[@jhenrip]

In 13:36 it's actually all the 60's that are added unnecessarily. You still need those 'g' in front of Tommy and Trxei (?) as a reference to the lookup table as you do after by erasing the 60's. Great videos, keep them up!

[@olusola7924]

No. You don’t exactly need the g except it’s the primary key. Keep in mind that the look up table can have more than 2 columns as he goes on to explain. Every extra column that the look up table translates to tons of repetitive data saved off the other table.

[@LVNeptune]

Issue with 14:55 to 15:35 still exists.

[@mohammedfalih8713]

this tutorial deserves the epic membership 😂

[@sufiyaanrajput7533]

14:50 you know what I mean, yeah! Anyways very good info and I remind myself to like each of the videos in this series...

[@whereweregoingwedontneedey4374]

Have to say mate, thoroughly enjoying watching these tutorials. A do have a question though (and bear in mind that I not a technical database architect) - doesn't cross referencing between multiple tables slow processing down when wanting to search for things and display things - for example, say a database is split in to 2 tables, customer information, and orders, and someone wants to see which location is which products then sales information is having to continually cross reference to the customer table to see the location of that customer. (maybe this is discussed in a later video, or other series?)

[@codebreakthrough]

Paul French Showing results from 2 tables is known as a join. It can slow things down, but it is usually still recommended!

[@whereweregoingwedontneedey4374]

Thanks. I'm at video 24 - new(ish) to all this and enjoying the series. By new, I mean new to the formal aspects of this, most of the theory I've picked up anyway over the years.

[@shetkarabhijeete0762]

Your videos are too good caleb! Keep up the Good work!

[@monaisazad34]

Hi Caleb? do you have any video about data warehouse design? ETL, star schema, OLAP?

[@POSyTIV]

Very good explanation! I admire your aparent short age with this knowledge, great!

[@mahesh97413]

Very nice explanation. Tq

[@blueonblack83]

Danke für den Upload!

[@HerppDerp]

Lets look it up in the table... Congratulations it a pretty good video.

[@Beautylovetoshop96]

THANK YOU

[@riderblack6401]

Thank you Caleb!

[@whereweregoingwedontneedey4374]

Regards to lookup tables, I have to say, I'm not sure what the difference is between a "lookup table" and a standard parent child relationship and putting "Membership ID" against the member name (like seen in some of the earlier videos). Are you (or anyone else) able to elaborate?

[@codebreakthrough]

Paul French You would likely never change a look up table. For example, Sex can be male or female. Some people decide to use a look up table for this.

[@whereweregoingwedontneedey4374]

Thanks, so is the benefit it bring due to the amount of data (in characters) being stored in the database? Does higher amount of data (in characters) result in significantly more work when searching or editing the data?

[@codebreakthrough]

Well not always, because in creating the look up table you will have to create a foreign key, which slows down the database. So it really just depends on how much data you're saving. It all has to do with a balance of how much performance vs how much integrity. By integrity, I mean by having the look up table you are also going to help prevent a lot of data inconsistencies, etc. Like if you have a membership website and the membership status options are gold, silver, and bronze. And then later you decide to change their names or something, you will only have to update the lookup table one time rather than change it throughout the entire customer table. Also in this situation with a look up table, you can add columns about the membership such as membership fee. Getting rid of the membership status look up table and putting the membership fee within the customer table would be bad because the price of the membership has nothing to do with the individual customer... Sorry for the long explanation, but hopefully that helps! :D

[@shathaqatinah2400]

Thank you

[@umutmetin6867]

Caleb, thank you for your sharing.

[@rajanburad5143]

Nicely made. I have a question. We are making lookup tables so as to avoid complexity, but then the ids present in membership table will be present as a reference in user table right? So in user table that id will get repeated like if there are 4 people with gold(having 1 as a value) then for each of them 1 will be repeated in 4 rows.. so isn't this a complex way? Please help!

[@codebreakthrough]

+Rajan Burad there will be a repeating field of 1, 2, etc, yes. Do the value of membership could be 1 for 3000 rows. Even so, this is probably the best way to do it. Some people will force the one to many relationship to be an intermediary table (normally used for many to many). This does not get rid of the repeated data, but now it is moved to its own table and doesn't pollute the user/customer table. It all depends on what you want and are trying to accomplish. I personally don't think it hurts to have that data in the user table. Maybe for bigger data (such as profile pic) force it into a different table of its own to keep the user table rather small.

[@rajanburad5143]

Great!! Thanks for the verbose :)

[@pamelarobinson67]

Very informative! Thank you!

[@zyxwvutsrqponmlkh]

The easy part is designing it, the hard part is building your select statements to use it.

[@richard2845]

Great explanation. Thanks

[@Oak_360]

watching from Ghana in 2022

[@ssdusd]

Very good, Thank you very much

[@dgetzin]

In this video, you actually look a lot like Stillicho.

[@KushalPradhan1]

good job....I really like your video...looking forward to your next video on database design

[@codebreakthrough]

Kushal Pradhan Stick around!

[@user-wy4el6zu1f]

Hello. Should i use ID in a lookup table or not? To be honest it seems a bit of a pain for me to use them with hibernate in javafx. I know that i will not be able to modify the records if i make the varchars a PK, but i'm ok with that. Please help. Thank you and thnx for the tutorial. Great job.

[@AndrewSymons]

Very good video, thank you. I subscribed and will be sure to watch your other videos on topics I don't 100% understand :)

[@peto813]

Thank you, very helpful.

[@nikhilmunj108]

You are "too genius" mate.

[@tin2001]

Watching in 2018.... Male and female definitely needs a lookup table now, and Russia might well be seen as a state of the US now 😋

[@xiaocheng1937]

Hey Caleb first of all thanks for the great vids. So after watching this video, it seems that the lookup table can also be used to implement domains? Is that a correct assumption? Thx

[@hoohoopotahrebus1760]

hey Caleb, what camera do you use and does it have a mic built into it(or do you have a separate mic)?

[@VeraxMusic]

I'm surprised that the word "variable" or "placeholder" doesn't come up at all when people are explaining this.

[@codeninja7306]

Thanks :)

[@Grantp1962]

I know these tutorials are 4 years old, but I'm finding them really helpful, thanks for all your time and effort in putting this series together. I do have a question about lookup tables though, in your example 'Membership Type' of 'Gold', 'Silver', 'Bronze', 'Platinum' it is fairly easy to to remember the ID (key) for just these 4 things (1,2,3,4). You then go on to say this could be used for the 'State' in an address then you have to remember the ID (key) for all the 50 states, surely this could lead to 'Bad Data' if someone entered the incorrect State ID. I am very new to all this so maybe i'm missing something here. This is pretty relevant to something I'm trying to do for myself but in my lookup table (list of shops where I've purchase PC equipment) I have well over 100 stores and no way can I remember each stores ID. Anyway thanks again for these tutorials and good luck with your new job with IBM you are obviously a very talented guy.

[@codebreakthrough]

Grant, this is such a good question. I'm disappointed I failed to address this in the video. The important thing here (or should I say KEY thing, lol) to realize is that a database often backs some UI. More likely than not the lists of your stores would be loaded into a drop down list to be chosen from. The NAME would be displayed, not the id. As a general rule, the ID shouldn't be involved from the users perspective. It is purely for wiring things up on the back end. So In an application you might have store.name and store.id. The name is used for display and the ID is used for the database. If you are working with the database directly and not through an application, issue a simple command like: SELECT * FROM StoreLookup WHERE name = "enter store name here" This will return the correct name and ID.

[@Grantp1962]

Thank you for the prompt reply. I had used 'Drop Down List' but I was not writing the id to the DB just the name, I knew this was wrong as if I changed the name in the list this wasn't reflected in the DB. NOW it DOES, so thank you for your help very much appreciated. As I say I am very new to all this I.E. I started yesterday with no DB or programming experence.

[@jacklythgoe1478]

16:30 Feminists getting mad at this point

[@Ruchird979]

Epic video :P (y)

[@joshuadalley9212]

I agree with everything you are saying, but in this case. You are taking it to the extreme. Yes you are fallowing the normalization rules. But only having 1 field of data with a PK is a waste of a look up table, and the person maintaining the DB will have lots of trouble since he will be force to do multiple joins just to get the data. But in the case for like Country where the PK is a business key = Abbreviation and having a field with the full country name is a good example of a look up table.

[@jamesstark4136]

Please just keep listing members. LMAO

[@Delta46853]

nice video

[@jules-gt1cv]

Got told to do it, so...

[@codebreakthrough]

Want to know what a Look up Table is? Check out this video and make sure you "like" it! buff.ly/1sc70WY

[@emilkarimov4310]

Russia)))))))

[@elkhanbrillianshah6375]

Guy is a bit weird dyt