MySQL: Automatically delete row on a connecting table

[imagined]

I'm reading about foreign keys and constraints. Very eye-opening. If there is a book you recommend to learn more about this please let me know. I'm very tempted to buy Pro MySQL or The Definitive Guide to MySQL 5 to learn more. Or if there is a very good well-explained, detailed tutorial please also let me know. I'm going over one of my books: PHP and MySQL Web Development but it just tells you what they are and gives you ONE brief example. It doesn't even go into detail at all.

Ok so, this is what I'm trying to figure out.

Let's say I have a clients table and a clientsContact table:

CLIENTS TABLE

• clientid
• firstname
• lastname

CLIENTSCONTACT TABLE

• clientid
• address
• city
• state
• phone

The book I have and a tutorial I read tells you how to establish a constraint using foreign keys. But what I want is for example:

• if I add a record to clients table, add a record to clientscontact table even if the records on clients contact will be empty.
• and viceversa: if I delete a record from the clients table, also automatically delete the record on the clientscontact table.

Thanks in advance

[actionAction]

For bullet one, use a TRIGGER http://dev.mysql.com/doc/refman/5.0/en/triggers.html

For bullet two, use DELETE CASCADE http://dev.mysql.com/doc/refman/5.1/...nstraints.html

Out of curiosity, why are you separating people from their address? in terms of normalization, this is incorrect. If this is just an example, nevermind.

[imagined]

Originally Posted by actionAction

Out of curiosity, why are you separating people from their address? in terms of normalization, this is incorrect. If this is just an example, nevermind.

I thought it was good practice to separate them. Client data and Contact Information data.

There was a database analyst that gave me a database and I was supposed to develop a Web app around that database that said names and contact data should be separate.

But then there was a programmer I was developing a database synchronization Web app that told me names and contact information should be on the same table.

In your opinion, there should be on the same table?

[actionAction]

This isn't an exact science and you will surely find many opinions. Logically speaking, contact information is a function of a given client. If a client has many address, city, state and phone numbers you want to associate them with, then your model could be correct as you don't want repeating rows or columns. Also, if your contacts can share an address, you would be correct in assuming that they should be separate.

I think rather than buying a book about MySQL, you should get a book about relational databases (I will try to find the textbook I used when I was in school to give you the title, it was very good). Set theory, normalization and functional dependence are at the core of database architecture, hopefully Wikipedia will provide some insight into your problem.

From Wikipedia: http://en.wikipedia.org/wiki/Functional_dependency
Given a relation (table) R, a set of attributes X in R is said to functionally determine another attribute Y, also in R, (written X → Y) if and only if each X value is associated with precisely one Y value. Customarily we call X the determinant set and Y the dependent attribute. Thus, given a tuple (row) and the values of the attributes in X, one can determine the corresponding value of the Y attribute. For the purposes of simplicity, given that X and Y are sets of attributes in R, X → Y denotes that X functionally determines each of the members of Y - in this case Y is known as the dependent set.

Read over that and let me know what your thoughts are.

[imagined]

Originally Posted by actionAction

Read over that and let me know what your thoughts are.

Thanks! I couldn't today but I sure will