entity relationships setup

[proctk]

Hi

I have setup a data base and from reading and feedback on forums. My design is not great and causing me some greif. I have five tables and U want to get join then for certain query.  I read that I should creat an assocate table which had the PK for each table in it. 

The part that I'm having a hard time understanding is how to populate the PKs in the assocate table as FK. 

Once this s done, how would I use the associate table to pull the information out of the tables

any help or times is great. 

tables

[Code]
[u]
children[/u]
child_id(pk)
childfirstname
childlastname
childdob


[u]sibling[/u]
sibling_id(pk)
sibfirstname
siblastname

[u]
spouse[/u]
spouse_id(pk)
spousefirstname
spouselastname

[u]users[/u]
user_id(pk)
userfirstname
userlastname
username

associate table
[i]aboutMe[/i]
aboutme_id(pk)
child_id(fk)
spouse_id(fk)
sibling_id(fk)
[/code]

[fenway]

I'm not sure what you mean by "populate" -- when you insert a record into the child table, you'll need to know / look up it's parent UID.

[proctk]

here is some more information.  I have learned a bit more but having a hard time creating the table


Below is code that I'm trying to use to create a master table.

the code returns error:
Create table Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (parent) REFERENCES parent(parent_id) FOREIGN KEY (sibling) REFER' at line 4

Anything standing out.

Also, once this works will it auto copy the information from tables that is haveing data added or do I have to edit my code to send information to the tables to send  the PK to the master table

any help guidance is great. As you tell my knowledge is limited


[code=php:0]

<?

include 'db.php';

mysql_query("CREATE TABLE master (
  master int(25) NOT NULL auto_increment,
  FOREIGN KEY (users) REFERENCES users(user_id)
  FOREIGN KEY (parent) REFERENCES parent(parent_id)
  FOREIGN KEY (sibling) REFERENCES sibling(sibling_id)
  FOREIGN KEY (children) REFERENCES children(children_id)
  PRIMARY KEY  (master)
)")or die("Create table Error: ".mysql_error());


?>
[/code]

[fenway]

You need to have those FK columns actually created in this table before you can link them.

[proctk]

I'm trying to create the table with the pode I posted. I'm having a hard time creating the table with the FKs

note the error message I posted above

[fenway]

Like I said, you're asking for a FK constraint on columns you haven't created.

[proctk]

HI

I have edits my tables as such.

Does this look right. If so will the table master automaticly be updated with the information from the linked tables.



[code=php:0]
mysql_query("ALTER TABLE master

ADD FOREIGN KEY (parent_id) REFERENCES parent(parent_id)
") or die("Create table Error: ".mysql_error());



[/code]


table design (all are similar)
[code=php:0]
include 'db.php';

mysql_query("CREATE TABLE children (
  child_id int(25) NOT NULL auto_increment,
  childfirstname varchar(25) NOT NULL default '',
  childlastname varchar(25) NOT NULL default '',
  childdob varchar (25) default '',
  childsex varchar (25) default '',
  CONSTRAINT child_pk PRIMARY KEY (child_id)
 
)")or die("Create table Error: ".mysql_error());


?>
[/code]

[fenway]

I'm fairly sure you need the proper ON UPDATE CASCADE option in order to have the changes propogate, but it's been a while since I've used FKs.

[proctk]

sorry not shure what your refering to [quote]you need the proper ON UPDATE CASCADE option[/quote]

[fenway]

You should definitely read over the relevant refman pages at this point, otherwise, we'll be going around in circles.