Re: Foreign Key question

From: Dave Clarke <pigwin32(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Foreign Key question
Date: 2009-06-03 21:32:34
Message-ID: 31c685c3-9326-4a84-b414-365016c03026@z7g2000vbh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Jun 3, 1:04 am, wmo(dot)(dot)(dot)(at)potentialtech(dot)com (Bill Moran) wrote:
> In response to Dave Clarke <pigwi(dot)(dot)(dot)(at)gmail(dot)com>:
>
>
>
>
>
> > I have a table that I'm trying to refactor and I'm by no means a SQL
> > expert (apologies if I'm posting to the wrong group). The table in
> > question has a column that allows NULLs. I want to move that column
> > into a separate table and set up a FK reference back to the original
> > table. My question is whether this is the correct way to refactor this
> > table.
>
> > Original table (other columns elided)
>
> > PurchaseOrder
> > ---------------------
> > POType
> > PONum
> > ServiceProviderNum
> > WorkOrderRef (NULLs allowed)
>
> > PK: POType + PONum
> > Candidate Key: PONum + ServiceProviderNum
>
> > Proposed structure
>
> > PurchaseOrder
> > ---------------------
> > POType
> > PONum
> > ServiceProviderNum
>
> > PK: PONum + ServiceProviderNum
>
> > WorkOrder
> > ---------------
> > PONum
> > ServiceProviderNum
> > WorkOrderRef (NULLs not allowed)
>
> > PK: PONum + ServiceProviderNum
> > FK: PurchaseOrder( PONum + ServiceProviderNum)
>
> > Does that make sense? My intention is to be able to join PurchaseOrder
> > and WorkOrder to get the set of PurchaseOrder's that have been
> > assigned WorkOrderRef's. As I understand it, FK's are generally used
> > for 1 to many relationships where as this is expressing a 1 to 1
> > relationship.
>
> > I would be very grateful for any assistance with this. Thanks, Dave
>
> You can certainly do what you're describing and it will work well.  I
> am curious as to why you'd want to, though.  What problem are you trying
> to solve by doing this?  I don't see it being worth the extra complexity
> and size you've added to the schema.
>
> --
> Bill Moranhttp://www.potentialtech.comhttp://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Thanks for the responses. I suspect I'm trying too hard, e.g. Bill's
comment re extra complexity. I'll have a ponder over the next couple
of days. I'm more of an OO guy and I'm working in an environment with
an existing ORM product. I want to feel confident I've got the
relational structures correct.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2009-06-03 21:36:37 Re: High I/O writes activity on disks causing images on browser to lag and not load
Previous Message Jennifer Trey 2009-06-03 21:21:14 Re: High I/O writes activity on disks causing images on browser to lag and not load