Database Design 22 - Look up Table 

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

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

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

Stop Bullying You are too old to bully

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.

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

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.

Kushal Pradhan Stick around!

+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.

Great!! Thanks for the verbose :)

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.

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?

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

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.

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.