Jump to content

entity relationships setup


proctk

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.