Welcome to Byte Yoga, my blog about Coding, Computers and IT.
Spysafe.com.au and Codewiz.au have become Byte Yoga!
View My Programmer and Full Stack Developer Portfolio.
About Byte Yoga – IT, Coding, tech, security, careers, and stuff.
Using a Junction Table for a Many to Many Relation in SQL
Using a table with only 2 fields to link a many-to-many relationship
I thought this Wikipedia page was helpful for this
https://en.wikipedia.org/wiki/Associative_entity
Like this
Quoted from the wiki page (I made the font of the punch-line sentence bigger):
An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation (or base table) to resolve many-to-many relationships. A base relation representing this kind of entity is called, informally, an associative table [or junction table].
As mentioned above, associative entities are implemented in a database structure using associative tables, which are tables that can contain references to columns from the same or different database tables within the same database.
The PK of the associative table is typically composed of the FK columns themselves.
Associative tables are colloquially known under many names, including association table, bridge table, cross-reference table, crosswalk, intermediary table, intersection table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, pivot table (as used incorrectly in Laravel - not to be confused with the correct use of pivot table in spreadsheets), or transition table.
This is a great post - junction tables! Some of these ER diagrams were doing my head in the beginning, showing many-to-many relationships directly between two tables, which you cannot do in Microsoft Access without an associated/junction table.
My question, why do people make diagrams showing relationships that you cannot make in database software? This site for example. Do other database software tools allow the creation of many-to-many relationships directly between tables without the use of a junction table?
As far as I know, in terms of a traditional "relational database" like that would use SQL (like nearly every "normal" [as in "ordinary/usual/common", not "normal" referring to "normalisation" lol though that also would apply here I guess] database was from the 1970s up to fairly recently when there are some newer styles which have become popular esp with "big data"), I think it (the junction table) is the only way it can be done. I know almost nothing about some of the newer tech like "NoSQL" or how things are done with that, it might be possible there(?)
Re why would anyone draw the ER that way - other than the possibility of my last sentence above (which is prob a good idea to research when there's time, esp since I noticed there are somel job ads which mention NoSQL... as a test I just typed nosql into seek.com.au and there are 306 jobs that mention it currently, as compared to 11855 jobs if you type sql), I think people would draw them like that only as a presentation tool for non-tech people (like some of management or clients) who want to see the top-level structure but don't know or need to know anything about the technical implementations.
"A relational database requires the implementation of a base relation (or base table) [aka junction/linking/associative/middle table] to resolve many-to-many relationships."
Easy Clock With Seconds On Windows 11
This is the best method I could find to easily obtain for myself a precious and coveted semi-Windows-10-style clock on Windows 11. Microsoft seems not to want us to know the time in seconds. I'm not...
Mechanical keyboards have become more popular than ever. Yet many are prone to chatter —keystrokes registering more than once per keypress. That is, unwanted repeated letters as you type. The basic problem is that all mechanical...
Stuxnet and the Birth of Cyber War
Most people are aware of the horrors of nuclear war. However there is much less awareness of the civilisation-ending potential of cyber war. Stuxnet is one of the most famous and sophisticated pieces of malware...