2 Replies - 573 Views - Last Post: 17 December 2015 - 08:53 AM

#1 kyle01  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 194
  • Joined: 26-November 14

One-To-One Relationship

Posted 17 December 2015 - 03:28 AM

I am creating a Entity Relationship Diagram, I have a One-To-One Relationship, but I am stuck on whether I would create a new table that joins to both existing tables?

My 2 Tables are; Student and FYIP. They both have a PK and attributes, they are linked by a 1:1 relationship. From what I understand, is that you combine both entities into one relation, then select one of the original entities a a PK of the new relation.

Is this correct?

Thanks,

Is This A Good Question/Topic? 0
  • +

Replies To: One-To-One Relationship

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: One-To-One Relationship

Posted 17 December 2015 - 06:29 AM

I would add a column to the table that makes the most sense to hold the primary key for the other. A joining table opens up a one to many relationship.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: One-To-One Relationship

Posted 17 December 2015 - 08:53 AM

You don't see a lot of one-to-ones in ERDs, because what would be the point?

Consider:
Table: PERSON
PERSON_ID: PK
LAST_NAME
...

Table: EMPLOYEE
PERSON_ID: PK FK
POSITION
...




This is still a 0..1 or 1..1 from EMPLOYEE to PERSON and that's really the best you're going to do with tables and constraints.

Since this is Oracle... you could put an after insert trigger on PERSON that creates an EMPLOYEE record immediately after a PERSON record is created. That's probably as close to 1..1 you'll get in an RDBMS.

Oracle actually has this wonky feature called a nested table which might sound like a logical choice, but I'd advise against it.

Similarly, Oracle also has a Record data type. While I'd also avoid this, it seems to meet your needs.

Why avoid the two oracle only options? Well, they're oracle only. Also, if you can't model your data in tables and rows you're probably doing it wrong. If you do use some extra special feature any third party tools you use, including connectors like jdbc, odbc, oledb, etc, might hate you.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1