OK, I think I got it now. Thanks for the additional info, that helps alot.
Rhino wrote:
----- Original Message ----- From: "Mike Blezien" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Friday, February 17, 2006 7:49 AM
Subject: Database design help
Hello,
we currently have a small database setup for affilates and
visitor/leads. I believe we have a "one to many" application, one
affiliate can have several visitor/leads but each visitor can only be
assigned to one affiliate.
What I need to know if this the best design for this setup. Basically
a visitor fills out a form, and is assigned to one affiliate. So I was
wondering is it better to create a " joining table" between the
`affiliates` table and the `visitors` table or will this design be
efficent as it is. Below are the 2 tables in question
CREATE TABLE IF NOT EXISTS affiliates (
affid int(6) unsigned NOT NULL auto_increment,
affiliate_id int(10) unsigned NOT NULL default '',
affiliate_email varchar(60) NOT NULL default '',
PRIMARY KEY (affid),
KEY affiliate_id (affiliate_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS visitors (
visitorid int(6) unsigned NOT NULL auto_increment,
fname varchar(20) NOT NULL default '',
lname varchar(20) NOT NULL default '',
phone varchar(20) NOT NULL default '',
email varchar(60) NOT NULL default '',
state char(2) NOT NULL default '',
ip varchar(20) NOT NULL default '',
dtime datetime NOT NULL default '0000-00-00 00:00:00',
exported varchar(10) default NULL,
affid int(6) unsigned NOT NULL default '0',
PRIMARY KEY (visitorid),
KEY email (email),
KEY affid (affid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
When you say "joining table", I assume you mean an "intersection table"
or "association table", which are the more common terms that describe
something used to break a many-to-many relationship into two one-to-many
relationships. I've never heard it described as a "joining table" but I
_think_ we're talking about the same thing....
In any case, I don't think you need anything but the two tables you have
here. If there is only ever going to be a single affiliate assigned to a
given visitor, then this is a one-to-many relationship and there is no
need for an additional table. However, I would suggest one small
amendment to your visitors table. Add the clause:
FOREIGN KEY (affid) references affiliates(affid) on delete <INSERT A
DELETE RULE HERE>
This will ensure that you never add an affid other than a value found in
the Affiliates table to the affid column of the visitors table. It will
also ensure the proper behaviour when deletes take place in the
affiliates table. For example, if you use ON DELETE CASCADE as your
delete rule, if one of the affliates is deleted from the affiliates
table, all of the rows with his ID will also be deleted from the
visitors table. If you use ON DELETE RESTRICT, you will not be able to
delete an affiliate from the affiliates table unless all of the Visitors
rows with his ID have had their affid changed to that of some other
affiliate. If you use on DELETE SET NULL, you can freely delete
affiliates even if they have rows in the Visitors table; the Visitors
rows will just have their affids set to null, which effectively means
that those Visitors have no assigned affiliate.
--
Rhino
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
