Hi.
It's simply that you can look at (a_id,b_id) as a PRIMARY KEY. You
don't need an additional, aritificial sequence number ab_id.
CREATE TABLE table_ab (
a_id INT NOT NULL,
b_id INT NOT NULL,
PRIMARY KEY (a_id,b_id),
UNIQUE (b_id,a_id)
)
Replace INT by whatever type seems appropriate (but probably INT is
okay). The primary key (which implicitly is a UNIQUE KEY), already
speeds up searches by a_id. The second index is there to speed up
searches by b_id. You could replace it with INDEX(b_id), which would
be enough, but my proposal has the advantage that it is a bit faster
in exchange for a bit more disk space needed.
Bye,
Benjamin.
On Wed, Jan 24, 2001 at 06:43:27AM -0500, [EMAIL PROTECTED] wrote:
>
> Thanks.
> (I don't know where I was coming from with the JOIN stuff, should have gone
> to bed hours ago)
>
> However, you don't think I should have the "ab_id" column, which would be the
> standard serial primary key for a table. Does a junction entity table not
> need it's own record ids? Technically, it shouldn't, but does good db form
> require it? or is a junction entity normalization table exempt from that?
>
> thanks again
> --Dylan Shea
>
> "Carsten H. Pedersen" wrote:
>
> > > I'm afraid this borders on sql/db question material vs. specific mysql
> > > material, but it stems from what I've read in the O'Reilly mysql book.
> > >
> > > If I have a db, which has two tables that have about 15 fields each.
> > >
> > <cut>
> > > and they have a many to many relationship between them. Should I
> > > a. Create a normalized junction entity.?
> >
> > Yes.
> >
> > > table_ab
> > > ab_id
> > > a_id
> > > b_id
[...]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
