On Jun 3, 1:04 am, wmo...@potentialtech.com (Bill Moran) wrote: > In response to Dave Clarke <pigwi...@gmail.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...@postgresql.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
