Join devRant
Do all the things like
++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatar
Sign Up
Pipeless API
From the creators of devRant, Pipeless lets you power real-time personalized recommendations and activity feeds using a simple API
Learn More
-
The 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?) -
@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 :/
(tldr: are foriegn keys good/bad? Can you give a simple example of a situation where foriegn keys were the only and/or best solution?)
i have been recently trying to make some apps and their databases , so i decided to give a deeper look to sql and its queries.
I am a little confused and wanted to know more about foreign keys , joins and this particular db designing technique i use.
Can anyone explain me about them in a simpler way?
Firstly i wanted to show you this not much unheard tecnique of making relations that i find very useful( i guess its called toxi technique) :
In this , we use an extra table for joining 2 tables . For eg, if we have a table of questions and we have a table of tags then we should also have a table of relation called relation which will be mapping the the tags with questions through their primary IDs this way we can search all the questions by using tag name and we can also show multiple tags for a question just like stackoverflow does.
Now am not sure which could be a possibile situation when i need a foriegn key. In this particular example, both questions and tags are joined via what i say as "soft link" and this makes it very scalable and both easy to add both questions and new tags.
From what i learned about foriegn keys, it marks a mandatory one directional relation between 2 tables (or as i say "hard a to b" link)
Firstly i don't understand how i could use foriegn key to map multiple tags with a question. Does that mean it will always going to make a 1to1 relationship between 2 tables( i have yet to understand what 11 1mant or many many relations arr, not sure if my terminology is correct)
Secondly it poses super difficulty and differences in logics for adding either a tag or question, don't you think?
Like one table (say question) is having a foreign key of tags ID then the the questions table is completely independent of tag entries.
Its insertion/updation/deletion/creation of entries doesn't affect the tags table. but for tag table we cannot modify a particular tag or delete a tag without making without causing harm to its associated question entries.
if we have to delete a particular tag then we have to delete all its associated questions with that this means this is rather a bad thing to use for making tables isn't it?
I m just so confused regarding foriegn keys , joins and this toxi approach. Maybe my example of stack overflow tag/questions is wrong wrt to foreign key. But then i would like to know an example where it is useful
question