Solved

oracle table question for one to many relations

Posted on 2013-01-23
6
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

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 35

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 35

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

739 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