Relational database question

Question

0.00/5 (No votes)

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

Jawanaut

Updated 6-Apr-17 17:38pm

Add a Solution

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

[no name] 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Peter Leow · Accepted Answer · 2017-04-06T17:38:00

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[^]

Posted 6-Apr-17 17:38pm

Peter Leow

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).