Re: Foreign Key question

In response to Dave Clarke <pigwin32(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 Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/