Mr. Froasty,

>From your note, it sounds like you want to use foreign keys; as Daniel
pointed out. I think an example would be helpful here. The subject of
foreign keys is bigger than a bread box so I'll just touch on the pieces
I think you'll find helpful. There is all sorts of literature scattered
about the web if you want to know more. Let's start with a fictional
case:

I work for a company with multiple departments each of which have one or
more employees. I would like a relational data structure to capture
departmental and employee information as well as preserve the
relationship between the two.

Make sense?

I create two tables: `Department` and `Employee`. Each table has a
primary key (as you illustrated in your example), which is unique per
record. <important>I add a column in Employee that holds the primary key
of the employee's associated department</important>. I then create a
relation between the two tables to indicate there is a relationship.

--Create the Department table
CREATE TABLE Department (
        IDDepartment INT NOT NULL AUTO_INCREMENT, 
        Name VARCHAR(35),
        PRIMARY KEY (IDDepartment)
) ENGINE = InnoDB;

--Create the Employee table and simultaneously the 
--relation to Department
CREATE TABLE Employee (
        IDEmployee INT NOT NULL AUTO_INCREMENT, 
        idDepartment INT NOT NULL,
        Name VARCHAR(35),
        PRIMARY KEY (IDEmployee),
        INDEX IDX_idDepartment (idDepartment),
        FOREIGN KEY (idDepartment) REFERENCES Department(idDepartment) 
         ON DELETE CASCADE
         ON UPDATE CASCADE
) ENGINE = InnoDB;

MySQL can do all of this provided you're using the InnoDB storage
engine. MySQL's documentation has some helpful information on the
subject - see link below.

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

With me so far?

A few points specific to MySQL:

(1) Whatever field you chose as your foreign key, needs an index.
(2) You can add foreign keys after a table has been created using an
ALTER statement.
(3) The option ON DELETE CASCADE means that whenever the parent record
(i.e., the department) is deleted the related employees will be deleted
too.
(4) The option ON UPDATE CASCADE means that whenver the parent's key
record (i.e., the department) is updated the related foreign key record
will be updated too.
(5) There are options other than ON UPDATE and ON DELETE. Give'm a look.

Good luck, and welcome to the DB development club.

Cheers,
Adam

-----Original Message-----
From: mrfroasty [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 27, 2008 5:19 AM
To: php-db@lists.php.net
Subject: [PHP-DB] re:database tables relations advice

I am quite new to database designs, I have a problem in my design...I
can actually feel it, but I am not quite sure if there is a feature in
mysql or I have to solve it with programming.

Example:
CREATE TABLE A (
                user_id int(16) NOT NULL auto_increment,
                ..........other datas
                PRIMARY KEY (user_id)
               );

CREATE TABLE B (
                user_id int(16) NOT NULL auto_increment,
                ..............other datas
                PRIMARY KEY (contact_id)
            );

Question:
How can I declare that the user_id in my 1st table is related to user_id
in the 2nd table...actually I prefer to have it exactly the same user_id
in both tables....I think if those 2 entries are the same it will be
great, but I am not sure how to achieve this.

P:S
-Ofcourse I know that I can extract it from TABLE A and save it in TABLE
B....but is that a way to go???Because this issue arise in couple of
tables in my data structure that I am tending to use in my
application(web).
-I also know that its possible to  make just 1 big table with lots of
columns....but I read its not a good database design...

----->>>>>please advice, running out of ideas :-(

Thanks......


-- 
Extra details:
OSS:Gentoo Linux-2.6.25-r8
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash
Typo:40WPM
url:http://mambo-tech.net


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to