Well, a VARCHAR(1) would still allow more than two choices :) ... It just means a one length variable character length. "m", "f", "c", "u", ... all fit - but I do otherwise agree.
Hi, Great tutorial, but I have several questions: 1) Why did you implement 1-to-many relation between people and company by company_has_people table? Why not add a foreign key to the "people" table? Same for addresses, why not to add a foreign key to the "companies" and "people"? Is it some sort of normalization? Maybe something like keeping the coupling low? What possible benefits can it bring? 2) Why do you need a separate primary key in "companies_have_people"? As the relationship is identifying, the foreign keys are primary keys as well and they form a complex primary key, that is the combination of "people_id" and "companies_id" works as a two element primary key. Could you explain the reason of the separate primary key("company_has_people_id")? 3) Why did you leave Not Null constraints == false? To my opinion, prefixes, like "people_" are not necessary here, we use aliases or full table names in queries, so a query would look like SELECT some_fields FROM people p JOIN ... something WHERE p.people_first = "John". We already have that "people_" in the form of "p." or even "people.", if you don't like aliases. Such prefixes also mess the autocompletion tips up: like when you need some field, you write "p.people_..." and you have the complete list of all the fields, which slows your work. Thank you
Im having an issue I hope u can help with i tried to set the items to cascade and it crashed the software on linux and win with a hand created schema and reverse engineered schema from mysql via workbench
At 7:00 how did you achieve many-to-many relationship by using 1:1 connectors? If I do not create a middle table and just throw in the n:m relationship between company and people, the middle table will auto-create with FK auto-populated, but the relationship to the table will be 1:m from both tables. What is the difference? Also, you created the 3rd PK in the middle table. If I were not to create it and leave the 2 FK as primary for this table, would I achieve then the restriction that 1 company cannot have the same employee listed 2 times? Thank you very much! Great video!
Not entirely sure I understand your question. You state "If I do not create a middle table" then you say... "the middle table will auto-create". i.e., If not A, then A. can you clarify?
Hello, thanks so much for this great tut. I had the same question as Max T. What he is asking is that the association table between [people] and [company] that you manually created is joined by 1:1 identifying relationships instead of 1:n identifying relationship. So question that even I am not sure of is how does this achieve a n:m between [people] and [company]?? If you attempt to directly map a n:m relationship between [people] and [company], MySql automatically generates the association table and the association table is linked by 1:n not a 1:1 like you did. Kindly explain.
I think the poster doesn't understand ER data modeling ... which is obvious how he treats the relationships. To answer one part of the question, it seems that MySQL does not differentiate between 1:m and 1:1 ... because it does not put a unique constraint on the foreign key.