I am creating a many-to-many relationship between the MEMBER and INTEREST tables.
Do I need a column in the intersection table that represents a vote of “Yes”, or is the mere fact that a record exists in this third table enough to show a Member has a given Interest.
Here is my preliminary table…
MEMBER_INTEREST
----------------
- id
- member_id
- interest_id
- value ??????Thanks.
First off, such a table is called a Junction table, not an intersection table. It’s good to use the correct terms when you can so people know what you’re talking about. 
(an intersection in databases is when you query two tables and intersect the result, i.e., all rows that are in query A that are also in query B. I guess you can see why you calling something an intersection table can be confusing to others).
It depends on the kind of interest shown. If it’s purely a question of interested yes or no, then I would say the mere existence of the record indicates interest. If there is some sort of quantifier involved (somewhat interested, interested, very interested, etc), you would need an extra field.
Also, you don’t need an id column in that table, since the combination of member_id and interest_id form a natural primary key.
TL;DR you only need member_id and interest_id in that table.
You have some really unique ideas about databases…
Oh really?
Try this… Is there an official name for the many-to-many relationship table in a database schema?
So we agree on one thing!
That is your opinion.
There are NUMEROUS benefits to always having a Surrogate/Derived on every table, but I’m not going to get into a religious debate over this.
Just know that similar to your first point, “It’s a big world out there…” 
[quote=“rpkamp, post:2, topic:116221”]
First off, such a table is called a Junction table, not an intersection table. [/quote]that’s a bit harsh 
it’s called a relationship table, because it is necessary to implement a many-to-many relationship
i’ve also heard a half dozen other names for it – association table, linking table, intersection table, as well as all of the other variants listed in both the web pages that you linked to
interestingly (to me, anyway, because i learned database and writing queries on IDMS, before commercial relational databases), the “junction” terminology dates back to CODASYL standards, when it was used for exactly the same purpose – to implement m-m relationships
on ~every~ table?
“a foolish consistency is the hobgoblin of little minds” – ralph waldo whats-his-name
on a relationship table, a surrogate primary key would only be useful PROVIDED that the table has child tables…
… seeing as how you’d need a UNIQUE key on the pair of columns anyway
It wasn’t meant that way. I really didn’t know people would call it an interection table; that’s just the most illogical name out there imho.
anyway, my apologies to @mikey_w, I didn’t mean to be harsh or belittle you! I was just trying to help. Turns out I’m the one who needs help eh?
Well, your post did seem that way, but no hard feelings.
That is what is great about SitePoint - you learn all kinds of cool things from people with different perspectives and backgrounds!
Most.
It’s A LOT easier to reference a record in a Junction/Reference/Intersection table by a unique Integer ID versus several fields.
My overall approach is this…
Nearly every table has an AutoIncrement ID field as the PK. Where practical, I use Natural Keys as backup UK’s. That way you have the best of both worlds.
Anyways, this whole topic is another thread - just saying…
[quote=“mikey_w, post:8, topic:116221”]It’s A LOT easier to reference a record in a Junction/Reference/Intersection table by a unique Integer ID versus several fields.[/quote]only if it has child tables
which so few of them do
[quote=“mikey_w, post:8, topic:116221”]Anyways, this whole topic is another thread - just saying…
[/quote]
i encourage you to start it, because i shall defend this particular point vigorously
i encourage you to start it, because i shall defend this particular point vigorously
[/quote]
I like an easy-to-read ID for every record, unless it is maybe a lookup table with 5 entries.
AutoIncrement/ID fields add virtually no overhead, but do add value to any table, e.g.
1.) Easy-to-read identifier when visually scanning the table
2.) Unique Key for tables that don’t have one (e.g. Intersection Tables with no Unique Key)
3.) Guaranteed to be unique which isn’t always the case with Natural Keys
4.) Less likely to be changed on the backend than a Natural Key which may change
I never said that I don’t like or favor Natural Keys, because I do. But there is virtually no harm in have a defacto AutoIncrement ID on most tables in addition to the use of Natural Keys.
And most importantly, the ID that I added in my Intersection Table meets “my” needs/desires so it is indeed a good choice.
1.) yeah, a third number is easier to read than the other two already in the row
2.) if your relationship doesn’t have a unique key, ur doing it wrong
3.) oh, please – a unique key is unique by definition
4.) yadda yadda yadda, this does not apply to relationship tables
do you know what a clustering index is?
If you are using NATURAL KEYS then you wouldn’t have two other numbers already in the row…
There is no rule that an Intersection Table has to have a unique key via Natural Keys. An example would be a log table.
Discourse didn’t take my edit. Maybe go back and read it…
No I don’t.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.