Re: Foreign Key question

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.