Click here to Skip to main content
13,146,014 members (45,802 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have a specific, yet simple question. I have three tables in my SQL database. I want to use the primary key in the first one as the foreign key in the other two. Now, this is where I run into problems.

I have made all three tables with no problem. Basically I have companyData, contactData and repData. I'm pretty sure it's a one to many relation. Every company can have a contact and multiple reps. A rep and contact can have only one company.

My question is... even if I tie to tables together with a 'same' primary key, is there a certain way to pull the data from the database or will it just automatically associate the rep's ID with the company ID?

What I have tried:

Just creating the tables in the database and making the foreign keys.
Posted 6-Apr-17 9:41am
Updated 6-Apr-17 17:38pm
Comments
CHill60 6-Apr-17 15:49pm
   
What do you mean by "pull the data from the database"? You will need to use a JOIN - which one depends on what you are trying to query and from where
0x01AA 6-Apr-17 16:06pm
   
Quote: "Every company can have a contact and multiple reps. A rep and contact can have only one company."

1.) "Every company can have a contact"
companyData contactData 1:1 relation

2.) "Every company ..... can have multiple reps"
companyData repData 1:N relation

-> Rep and contact do _not_have direct relation

Some more Explanation needed I think.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Based on your description, there are at least three entities, first state clearly the what's of these entities based on the user requirements, e.g.:
1. What is a company, its purpose, and its data attributes?
2. What is a contact, its purpose, and its data attributes?
3. What is a rep, its purpose, and its data attributes?
4. What is the relationships between them, if any?
Use a diagram to represent points 1 to 4.
Use this as reference Introduction to database design[^]
  Permalink  
Comments
CPallini 7-Apr-17 3:25am
   
5.
Peter Leow 7-Apr-17 4:42am
   
Thank you, CPallini.
Jawanaut 7-Apr-17 13:48pm
   
Thank you for the reply. That was very informative. Unfortuantely it left me face with a new problem. When trying to create the constraints for the relations, the field will not submit to the database. Instead I get an error with the constraints.

I feel I may have set it up wrong. That link gave examples differently. It looks more like EER diagram than SQL. Either way, I'm not using either. I'm using phpAdmin's 'Foreign key constraint (INNODB)'.

Each table has an ID. Since two can be in the one but not related to each other, I made a column in both repData and contactData called companyID and made it relational to companyID in companyData table.

Now, just wondering if I'm doing it backwards... or completely wrong? I do really appreciate everything you (and everyone) have offered me so far.
Peter Leow 7-Apr-17 22:30pm
   
The purpose of foreign key constraint is to enforce referential integrity rule of the database. That includes not allowing a foreign key value in the children tables (rep and contact) without it first exists as primary key value in the parent table (company).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.170915.1 | Last Updated 6 Apr 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100