How do I properly reference tables

Question

4.00/5 (1 vote)

See more:

I have tables person which stores all information about a person.
Another table contact which stores contact info about table person.
Contact has column "contactid" which is referenced from table person.

And another table " emergency contacts" which stores emergency contacts of a person.
How do I achieve the following
1. the emergency contact of personA -(personB) may already exist in Person table who also have contact info. When personB contact info is deleted the reference in emergency table should be delete and also cascade upon update of personB's contact info.
2.the emergency contact of personA may not already exist in table Person.

Thanks.

Posted 19-Nov-15 13:19pm

kwaku Emma

Add a Solution

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)

PIEBALDconsult · Answer 1 · 2015-11-19T13:45:00

Solution 1

Have one person table to store all people and one table to store all the references. Before deleting a person record, delete all the references.

Person

Name  ...
Alice ...
Bob   ...


Reference

From  To  ...
Alice Bob ...

(I would never use names (or other strings) as IDs, but you get the idea.)

The reference table can also hold an indicator of what type of reference it is -- such as emergency contact, or authorized buyer, or whatever you need.

Posted 19-Nov-15 13:45pm

PIEBALDconsult

v2

Comments

kwaku Emma 19-Nov-15 20:19pm

Yes I get your idea but is it necessary to store those emergency contacts who don't have records in person (this stores records that have vital functions)

PIEBALDconsult 19-Nov-15 20:26pm

They are persons; you put them in person. You may have an indicator (perhaps bitmapped) for what kind of person, but all persons go in one table. Doing otherwise leads to unnecessary complexity. What happens when one person should exist in both tables? Or be moved from one to the other?
You asked for advice from experienced developers; this is the advice from an experienced developer.

kwaku Emma 20-Nov-15 4:42am

Sorry. I didn't mean to be rude but wanted know if my thinking is close to correctness. I appreciate your effort. Thanks