You are right, a comma separated "list" won't work since you won't be
able to do joins on it. To create a one to many relation, you actually
need to create another table to hold the relation.
CREATE TABLE listCatLink (
ListingID bigint(20) unsigned NOT NULL,
CategoryID int(11) NOT NULL
)
On Aug 4, 2004, at 10:35 AM, Erich Beyrent wrote:
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]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
