Solved

oracle table question for one to many relations

Posted on 2013-01-23
6
432 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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