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.
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.
@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!
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!
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)
@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.
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.
@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
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.
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
Sometimes its suggested that 3NF is not necessary in all situations (The example of the multiple URL's in a separate table).. The alternative would be a Column called 'websites' inside the user table.. That table has a list of websites, eg: 'mysite.com,other.com,another.com'.. Or perhaps, Serialized or JSON data -- for the most part it's up to you. What you determine to be best. In the example above I would personally make it a list rather than the 3NF.
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
hollerschos 3 months ago in playlist More videos from JREAMdesign
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.
Grkgermn333 4 months ago
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.
gml142 4 months ago
@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!
JREAMdesign 4 months ago
@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!
gml142 4 months ago
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!
Topsboy2 4 months ago
I screamed my monitor that you had an extra comma
frosty1433 4 months ago
@frosty1433 I wish I could have gone into the future and heard that HAHA
JREAMdesign 4 months ago
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)
dmonien 4 months ago
@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.
gml142 4 months ago
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.
xxaxx0 4 months ago
@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
Brookes729 4 months ago in playlist Videos from JREAMdesign
"I'm going to skip this because it is 2:00 a.m. & I can't process it." snicker. You are adorable. :)
TheLtUhura 4 months ago
@TheLtUhura lolz
JREAMdesign 4 months ago
nice. thank you
verymad 4 months ago
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
Brookes729 4 months ago
@Brookes729 Yeah thats another way to do it, it might actually be better doing it that way man, i dont know for sure
JREAMdesign 4 months ago
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
Chaos7703 4 months ago
@Chaos7703 Yes, my thoughts exactly
astev52 4 months ago
Which headset do you use? Is it the new or do you use a programm to optimize the sound quali?
Venistro 4 months ago
@Venistro I just bought a new 'Blue Yeti' thanks to a suggestion of a youtube guy. It is really good quality
JREAMdesign 4 months ago
Thankyou
whatsajaymo 4 months ago
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 :)
MakkeSk8 4 months ago
@MakkeSk8 Hahahahaha
JREAMdesign 4 months ago
Very nice. I appreciate your videos.
JamesRCoston 4 months ago
Sometimes its suggested that 3NF is not necessary in all situations (The example of the multiple URL's in a separate table).. The alternative would be a Column called 'websites' inside the user table.. That table has a list of websites, eg: 'mysite.com,other.com,another.com'.. Or perhaps, Serialized or JSON data -- for the most part it's up to you. What you determine to be best. In the example above I would personally make it a list rather than the 3NF.
JREAMdesign 4 months ago