Solved

oracle table question for one to many relations

Posted on 2013-01-23
6
419 Views
Last Modified: 2013-01-24
I have a created table

1. hospital
2. hosp_contacts
3. contacts

Hospital to hosp_contacts - one to many
Now I want to create another table contacts
Contacts will have one to many relationships to hosp_contacts.
Based on the erd that I attached, will my new table contacts have
contacts_id,
contact_id ( from hosp_contacts),
hospital_id ( from hospital).
Please let me know.
Hospital-erd.bmp
0
Comment
Question by:anumoses
  • 3
  • 2
6 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 38810070
What additional attributes will the new contact table have that the hosp_contact table does not?

I also don't like contact means stored in the same table as the name type information.  What if a persone has more than one email address?  Many people these days have more than one cell phone.  As soon as you start adding these up, the table becomes pretty ugly (2 or 3 email address columns, 2 or 3 phone columns, etc...).

You might think about splitting this out into a person_contact type table.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38810088
The table that you are creating that links HOSPITAL and HOSP_CONTACTS should only need 2 fields.

HOSPITAL_ID, which links back to HOSPITAL
CONTACT_ID, which links back to HOSP_CONTACTS

Creating an additional id in that table is not useful.  It will never be used anywhere.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38810353
The reason we have to do this is

hosp_contact table we will only store the contact id hospital id and the key is hosp_contact_id
The contact table will have one to many relation ship to hosp_contact table. And hospital will have one to many with hosp_contact
Hospital-erd.bmp
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Accepted Solution

by:
johnsone earned 125 total points
ID: 38810414
But, what is the point of HOSP_CONTACT_ID in HOSP_CONTACTS?  You are creating a key field just for the sake of creating a key field.  It is not referenced in any other table and you will never be querying on that field.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38810445
Thats true. I dont have to have. I thought of primary but as you said  only contact id and hospital_id are enough.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38814556
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 83
What is the version of ojdbc6.jar 2 39
passing parameters to sql script oracle 4 26
Oracle - SQL Where clause causing Invalid Number Error 4 27
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now