Jump to content

Archived

This topic is now archived and is closed to further replies.

proctk

entity relationships setup

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
You need to have those FK columns actually created in this table before you can link them.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Like I said, you're asking for a FK constraint on columns you haven't created.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
sorry not shure what your refering to [quote]you need the proper ON UPDATE CASCADE option[/quote]

Share this post


Link to post
Share on other sites
You should definitely read over the relevant refman pages at this point, otherwise, we'll be going around in circles.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.