Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
asgs788524dThe link table is a mapping of questions to tags (or vice versa, too). Now you make these two columns in this table REFERENCE the primary identifiers defined in the corresponding tables. Why? Because you can't establish a LINK between a non-existant question or a non-existant tag since it is not a valid link at all. Such a link is either outdated or meaningless
At the most basic functional level, FKs are a form of metadata a database will use to optimize its compilation and execution schedules for queries. They are also a kind of trait which comes with the benefit of default implementation. This will include some of what @asgs said.
- they enforce strong relationships, so you don't have to write that functionality in every query (DRY). It's up to you to decide if you prefer repetition to alter-table and little temp work on upgrades
- because they enforce strong relationship, they also enforce relational integrity so data doesn't get out of sync (less fragile)
- map/link tables
- FKs increase performance in scenarios where the database trusts the FK (it can optimize bc trust has an assumption of a light NO-CHECK)
- FKs enable cascade behavior, this would have to be written otherwise.
Thanks @SortOfTested, @asgs . I understand the fkey somewhat.
So basically foreign key is automatically making db do what would have been an extra query for toxi schema. I still have to find a practical example where toxi schema with its weak links is less appropriate than a foriegn key based relation .
(I believe Most of the times ( i am yet to find a single counter example) we do Not want to delete the associated table entries when main table entries are delete. Like it would be pointless to delete the tag 'android' when a question of Android is deleted, because someone else could ask an Android question and availability of Android tag would make a faster insertion i guess?)
gitlog668523dAs you are my senior wrt current year in college, I guess you've done DBMS course.
Relational databases are very very bad without a FK
For an example, we can see why we use 3NF/BCNF
Or basically what is the need of normal forms
@gitlog The sad reality of me, nd my 3rd tier college are :
(1) the content they teach is not complete enough to guarantee a kid's future, he/she gotta do extra stuff outside the course to come out as a valuable dev
(2) for some subjects whose content is good , they are lazy enough to not teach us any shit with incompetent teachers. even the exams they take are easy to crack with decent % using old sem papers as syllabus and even questions don't change
(3) I was incompetent enough to not focus on many subjects, rather taking the "just to pass" approach while doing other stuff
So i guess you are right. I have to go back read dbms from the basics :/