Solved

oracle table question for one to many relations

Posted on 2013-01-23
6
427 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 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…

756 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