MySQL Database Normalization

Posted by: Admin  :  Category: Mysql

Linux Cpanel shared hosting: 600 GB disk space, 6 TB bandwidth, free domain, unlimited databases and ftp accounts, web hosting cheap and pro at Hostony

Linux Cpanel shared hosting: 600 GB disk space, 6 TB bandwidth, free domain, unlimited databases and ftp accounts, web hosting cheap and pro at Hostony

I’ll show you a common strategy to improve your databases: it’s called Database Normalization. There are 6 levels, but going to 3 Normal Form (3NF) is sufficient. jream.com

Linux Cpanel shared hosting: 600 GB disk space, 6 TB bandwidth, free domain, unlimited databases and ftp accounts, web hosting cheap and pro at Hostony

25 Responses to “MySQL Database Normalization”

  1. hollerschos Says:

    once more laughing… what the heck is “joining a lot of crazy things”? lol
    man it’s still about data, hopefully useful ones… keep going

  2. Grkgermn333 Says:

    Is 2NF really how it’s supposed to be? That instead of giving data inside a user’s fields, to give an id that points to another table? Seems inefficient. Please explain how this is better than storing the variable in plain text, and fetching it with a single query.

  3. gml142 Says:

    @JREAMdesign If you’re interested, here is where I got my information: percona.tv/percona-live/mysql-at-facebook-current-and-future. The talk they give was interesting, but you may get more out of it than I did!

  4. JREAMdesign Says:

    @gml142 Hmm, Ive been told they dont use a relational database I may have heard wrong, I better not spread things im not certain of!

  5. gml142 Says:

    I just wanted to point out that I believe what you said about Facebook is untrue. Facebook does use NoSQL for some of their data, but they are actually the largest users of MySQL in the world if memory serves. They have even had a presence and given talks at the annual Percona conference on matters of SQL.

  6. gml142 Says:

    @dmonien Good points, I noticed that about the url’s also. However, I don’t believe the linking table will need a separate column for id. If designed properly, every row in the table will be unique, so the combination of userid and url can work together to form a key when needed. This will save an entire column.

  7. Topsboy2 Says:

    Thanks for this. I just finished doing this in Access and wasn’t too sure how to write it in SQL. Seems, only needed to use the INNER JOIN. Thanks anyhow and good example!

  8. JREAMdesign Says:

    @frosty1433 I wish I could have gone into the future and heard that HAHA

  9. frosty1433 Says:

    I screamed my monitor that you had an extra comma

  10. astev52 Says:

    @Chaos7703 Yes, my thoughts exactly

  11. Brookes729 Says:

    @xxaxx0 Usually a single sql query call is quicker then 2 calls, a useful tip is to index foreign keys (using phpmyadmin its quick and simple) this will allow the sql to find the values quicker. Only potential exception might be with a huge user table and a tiny school table, in which case the length of the repeated school strings may cause a longer delay then the second sql call but you then have the time required to compare the resulting arrays

  12. dmonien Says:

    I would argue that you should not have any column for websites in the user table. Instead have a one-to-many relationship to the websites table with just one url per row (id, userid, url) where id and userid is the primary key. This way a user can have any number of website-urls connected to it or none at all. In the video you have null fields, which is a form of redundancy. Also the user table has two values in name(first+last), this is a violation of 1NF.(If you wanna get really picky)

  13. xxaxx0 Says:

    Hey JREAMdesign,
    i’m using a slightly different technique, – I’m quering the diffrent tables and putting the info into objects/arrays that are passed throu, so for example if I was building this I would pass array schools with keys that are equal to the database id’s and whenever I query the main table i just substitute the needed data from my array. Is “join” better than mine in every case or just when the databases are small? and if yes – when should i use join and when should I pass arrays.

  14. JREAMdesign Says:

    @TheLtUhura lolz

  15. TheLtUhura Says:

    “I’m going to skip this because it is 2:00 a.m. & I can’t process it.” snicker. You are adorable. 🙂

  16. JREAMdesign Says:

    @Venistro I just bought a new ‘Blue Yeti’ thanks to a suggestion of a youtube guy. It is really good quality

  17. JREAMdesign Says:

    @Brookes729 Yeah thats another way to do it, it might actually be better doing it that way man, i dont know for sure

  18. JREAMdesign Says:

    @MakkeSk8 Hahahahaha

  19. verymad Says:

    nice. thank you

  20. Brookes729 Says:

    It is sometimes useful to look at the relationships between the field and the table ID, for example:
    One user has one school, one school has many “users”, so the school’s foreign key goes to the user table as shown.
    But with the websites as chaos says, one user may have many sites and a site may have many users, so it is useful to create another table with just the foreign keys of userID and websiteID.
    Other than that great guide

  21. Chaos7703 Says:

    Nice job. When you created the website table though I think you were still breaking 1nf. The website table should either be a table where the url & user.id are the pk or add an “association table” with two columns website.id & user.id. That way if a user only has 1 url, there’s not 2 null cells being stored. If a user has 4, 5, 6+ urls you can do that too. And if N user’s all share one site (e.g. faceb-com), you only store the name once instead of N times. =D

  22. Venistro Says:

    Which headset do you use? Is it the new or do you use a programm to optimize the sound quali?

  23. whatsajaymo Says:

    Thankyou

  24. MakkeSk8 Says:

    hahah when u failed that comma i screamed irl wtf ! u failed witht the comma! XD

    anyway this is very helpful for big databases and its stabler, very good tutorial!! I appreciate your videos also 🙂

  25. JamesRCoston Says:

    Very nice. I appreciate your videos.

Leave a Reply

*