I think you understand. Here is how I would re-write the query to use the
new table:
$query = "SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.MP3Name,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
l.DiscountID,
l.DiscountType,
l.DiscountAmount,
o.Alias,
l.Description
FROM listings l
INNER JOIN publishers p
ON l.PublisherID=p.PublisherID
INNER JOIN composers c
ON l.ComposerID=c.ComposerID
INNER JOIN arrangers a
ON l.ArrangerID=a.ArrangerID
INNER JOIN listings_categories lc
ON l.ListingID = lc.ListingID
INNER JOIN categories o
ON lc.CategoryID = o.CategoryID
WHERE o.Name='".$Category."'
ORDER BY ".$OrderBy;
(That's just the style I prefer as I can more easily spot which "match-up"
conditions belong to which sets of tables. That way I am less likely to
leave one out and accidentally create a cartesian product of any two
tables. The comma separated style you use is absolutely, perfectly valid.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Erich Beyrent" <[EMAIL PROTECTED]> wrote on 08/04/2004 12:39:55
PM:
> I think I understand. So instead of my queries being centered around
> the listings table, they will be centered around this new table?
>
> Currently, I pull the records for each category like so:
>
> $query = "select
> l.CatalogNumber,
> l.PDFLink,
> l.PDFName,
> l.MP3Name,
> l.Title,
> p.PublisherName,
> c.ComposerLname,
> a.ArrangerLname,
> l.Price,
> l.Description,
> l.DiscountID,
> l.DiscountType,
> l.DiscountAmount,
> o.Alias,
> l.Description
> from
> listings l,
> publishers p,
> composers c,
> arrangers a,
> categories o
> where
> l.CategoryID=o.CategoryID and
> o.Name='".$Category."' and
> l.PublisherID=p.PublisherID and
> l.ComposerID=c.ComposerID and
> l.ArrangerID=a.ArrangerID
> order by ".$OrderBy;
>
>
> To follow your example, I would add these other fields to the
> listings_projects table you defined below, and restructure the query
> around that?
>
> Thanks for your insight!
>
> -Erich-
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 04, 2004 11:51 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Design Question
>
> As posted, your data structure supports two one-to-many relationships,
> not
> the one-to-one relationships as you described. You can have multiple
> Listings per Category and multiple Categories per Group. What it sounds
>
> like you have been asked to do is to support a many-to-many
> relationship.
> You need to support both multiple Listings per Category and multiple
> Categories per Listing.
> As you have it now:
> Groups (1..*) Categories (1..*) Listings
>
> As you need it to be:
> Groups (1..*) Categories (*..*) Listings
>
>
> To create a (*..*) relationship between two tables, you need a third
> table. Each entry in this table represents one Listing-Category
> association (relationship).
>
> CREATE TABLE listings_projects (
> ListingID bigint not null
> , CategoryID int
> , ... any additional fields as needed ...
> , PRIMARY KEY (ListingID, CategoryID)
> )
>
> The primary key ensures that at each Listing/Category combination
> appears
> only once (no duplicate assignments). I showed you where additional
> fields
> can fit into the relation table because sometimes there are facts about
> relationships that do not fit into either of the tables they relate A
> recent example in this list was a relation table between chemical
> compounds and the various plants in which those compounds could be
> found.
> A fact that belongs to the *relationship* could be the concentration of
> that chemical in that plant. That concentration value would not belong
> to
> the plants table nor would it belong to the compounds table but does
> belong to the relationship of plant to compound. Make sense? I have
> personally used additional fields like those to indicate "deletion".
> That
> way "old" values are no longer available for new relationships (in my
> applications) and my queries won't break as I didn't actually get rid of
>
> any information. My "historical" reports still function as the "old"
> names
> are still in the system, even if you can't use the the old names for any
>
> current purposes.
>
> Yours,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
>
> "Erich Beyrent" <[EMAIL PROTECTED]> wrote on 08/04/2004 10:35:33
>
> AM:
>
> > Hi all,
> >
> > I need some advice on a project I have. Basically, I have some
> tables:
> >
> > CREATE TABLE listings (
> > ListingID bigint(20) unsigned NOT NULL auto_increment,
> > CatalogNumber varchar(12) NOT NULL default '',
> > PDFLink varchar(100) default NULL,
> > PDFName varchar(80) default NULL,
> > Title varchar(100) NOT NULL default '',
> > ComposerID int(11) default NULL,
> > ArrangerID int(11) default NULL,
> > PublisherID int(11) default NULL,
> > Price double(16,2) NOT NULL default '0.00',
> > DiscountID int(11) default NULL,
> > Description text,
> > NewTitles tinyint(1) default NULL,
> > CategoryID int(11) NOT NULL default '0',
> > PRIMARY KEY (ListingID)
> > ) TYPE=MyISAM;
> >
> > CREATE TABLE categories (
> > CategoryID int(11) NOT NULL auto_increment,
> > Name varchar(50) NOT NULL default '',
> > Alias varchar(60) default NULL,
> > DiscountID int(11) default NULL,
> > Description text,
> > GroupID int(11) NOT NULL default '0',
> > PRIMARY KEY (CategoryID)
> > ) TYPE=MyISAM;
> >
> > CREATE TABLE groups (
> > GroupID int(11) NOT NULL auto_increment,
> > Name varchar(50) default NULL,
> > DiscountID int(11) default NULL,
> > PRIMARY KEY (GroupID)
> > ) TYPE=MyISAM;
> >
> >
> > Currently, there is a one-to-one relationship between listings and
> > categories, and listings and groups. Now, the customer is requesting
> > that a listing be included in several categories. I am not quite sure
> > how to do this.
> >
> > My thought was to add a new field to the listings table that would
> > contain a comma-separated list of CategoryIDs, but something doesn't
> > feel right about this solution.
> >
> > What would be a good approach to this problem?
> >
> > -Erich-
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
