Newbie question about keys

I am trying to create a simple multitable database. The first table
keeps track of alumni of my association including an id#(key,
auto_increment), firstname, lastname, email and year they graduated.

I am hosting an event so I have an online registration for alumni and I
want them to tell me the name and relationship of upto 4 guests. So i
created a second table called guest with guestid guest lastname,
firstname and relationship as well as a column that has the alumniID of
the alumni they are coming with. I set up this table as follows

CREATE TABLE guest (
gid INT (50) PRIMARY KEY,
glastname CHARACTER (20),
gfirstname CHARACTER (20),
relation CHARACTER (20),
pid BIGINT (100),
CONSTRAINT pidFK FOREIGN KEY (pid)
REFERENCES alumni (pid)
ON DELETE CASCADE
);

I then use PHP and a form to enter data into both the tables. I am
using XAMPP 1.5.1 and when I use phpmyadmin to delete the alumni record
it keeps the guest record and that is not good because then the pid
section references an alumni that is not in the database. I feel like
it doesn't reset the alumniID and therefore it still thinks that a
person with ID still exists, but I could be wrong.

On a related note, what is the best way to make sure the same person
doesn't register more than once. I have it set so that the id number is
the primary key, should I make a field like e-mail unique and not
necesarilly primary?

Thank you for your help

Re: Newbie question about keys

bob.herbst@gmail.com wrote:
> when I use phpmyadmin to delete the alumni record
> it keeps the guest record

Are you aware that when using MyISAM tables, it accepts foreign key
declarations, but ignores them? It does not enforce referential
integrity, including the ON DELETE CASCADE, unless you use InnoDB tables.

> On a related note, what is the best way to make sure the same person
> doesn't register more than once. I have it set so that the id number is
> the primary key, should I make a field like e-mail unique and not
> necesarilly primary?

You could do that, or alternatively put a unique constraint on the
combination of the two columns glastname, gfirstname.

Any way you do it, there will be ways for people to sign up more than
once. Maybe someone uses their yahoo.com email the first time, but a
gmail.com address the second time. Or else they spell their name Bob
once, but Robert the second time. It's practically impossible to
prevent all such cases, so you need to include in the system some way
for a user to un-subscribe, or if you want to be really fancy, provide a
way for them to merge their two subscriptions into one (for instance, if
one has their correct contact details, but the other has the correct
guest list).

Regards,
Bill K.

Re: Newbie question about keys

>> On a related note, what is the best way to make sure the same person
>> doesn't register more than once. I have it set so that the id number is
>> the primary key, should I make a field like e-mail unique and not
>> necesarilly primary?
>
>You could do that, or alternatively put a unique constraint on the
>combination of the two columns glastname, gfirstname.

Even (first name, last name, alumniID) is unlikely to be unique in
the guest table. What happens when an alumni invites Mr. & Mrs.
Quetzel Katzenheimerrr as two of his guests? And in any size class,
it's likely more than one person will invite a John Smith.

Email addresses may be a problem because related people (and people
married to each other) may share the same email address (also maybe
address and phone number). Also be prepared to have Mr. & Mrs.
Henry Brown register as different alumni, which may well NOT be a
mistake, and they might have the same email, address, and phone
number.

I think it is more important to NOT refuse legitimate registrations
than to refuse duplicates. In the case of the guests, the alumni
are filling out the form, right? Show them a complete list of their
guests (4 max) and ask them if they want to add, delete, or change.
That will make duplicate GUESTs for the same alumni unlikely, and
hard to do accidentally. I don't think two different alumni inviting
the same guest will be that much of a problem (possible exception:
the two alumni are married to each other but don't communicate well.
They want to invite more than 4 people so they have to split up the
list between them).

>Any way you do it, there will be ways for people to sign up more than
>once. Maybe someone uses their yahoo.com email the first time, but a
>gmail.com address the second time. Or else they spell their name Bob
>once, but Robert the second time. It's practically impossible to
>prevent all such cases, so you need to include in the system some way
>for a user to un-subscribe, or if you want to be really fancy, provide a
>way for them to merge their two subscriptions into one (for instance, if
>one has their correct contact details, but the other has the correct
>guest list).

It is probably more important to be able to correct duplicates
(when detected manually) than to reject them when they occur. And,
as above, there are plenty of ways duplicate-rejection can fail to
reject real duplicates. But having the system reject NON-duplicates
is even more embarassing, especially when you can't fix it.

Gordon L. Burditt

Re: Newbie question about keys

Should I be using InnoDB tables, is there a way to switch storage type
at the command line, because my phpmyadmin, shows that the InnoDB is
loaded but not a valid choice for choosing as a storage engine type.

Can anyone recomend what type of storage engine to use. Everything I
have read says to use referential integrity and I am trying to keep
this enforced. Is this not that important?

Sorry for the newbie questions, I just want to do what is best in the
long run.

Thanks
Yanks

Re: Newbie question about keys

>Should I be using InnoDB tables, is there a way to switch storage type
>at the command line, because my phpmyadmin, shows that the InnoDB is
>loaded but not a valid choice for choosing as a storage engine type.

ALTER TABLE foo ENGINE=InnoDB;
ought to do it, assuming InnoDB is enabled. (This syntax works on
MySQL 5.0. Some earlier versions may have wanted Type=InnoDB instead
of ENGINE=InnoDB). It is possible it's compiled in but turned off
in my.cnf, and that may be why phpmyadmin doesn't offer it.

>Can anyone recomend what type of storage engine to use. Everything I
>have read says to use referential integrity and I am trying to keep
>this enforced. Is this not that important?

It depends on the application. Some applications don't use references
(like ones using a single table). Some use references but it's OK
if the record being referenced doesn't show up immediately and it's
not acceptable to have to manually sort the paperwork before entering
it. In some it's VERY important.

InnoDB does referential integrity and transactions, but it costs
you in speed. If you need referential integrity and/or transactions,
it's probably what you want to use.

Gordon L. Burditt