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]
