----- 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
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 16/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
