Benjamin, beautifully put. Thank you both so much.
--Dylan Shea
Benjamin Pflugmann wrote:
> 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
