Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle table question for one to many relations

Posted on 2013-01-23
6
Medium Priority
?
436 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 500 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 35

Accepted Solution

by:
johnsone earned 500 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

610 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