Correct on all counts. And yes the original system was inherited from
a dBase application and time constraints have meant that its not as
clean as it should be. However, unless things are "fixed" now they
become more difficult as time passes and other requirements are added
in to complicate matters, so its probably time to bite the bullet :-(
I'll look at your suggestions. (and have done the inserting as an SP,
so thanks also for that)
--
Alan J Davies



Quoting Helen Borrie <helebor@...>:

> At 11:19 PM 6/08/2008, you wrote:
>
>> Each supplier owns many farms. One farm can only be owned by one supplier
>
> Ah, so suppliers OWN farms, not supply them. ;-) And farms PRODUCE
> products, not get them from suppliers.
>
>
>>> I think SUPP_CODE does not belong in the FARM record at all, but in
>>> a separate intersection table between FARM and SUPPLIER, that has
>>> FKs to both tables.
>>>
>> This is a one-to-many relationship and I have a direct relationship
>> using the supplier code as the key in the farm table.
>>
>> supplier
>> supp_code
>> 1
>> farm
>> supp_code farm_code
>> 1 101
>> 1 102
>> 1 103
>> supplier
>> supp_code
>> 2
>> farm
>> supp_code farm_code
>> 2 201
>> 2 202
>> 2 203
>>
>> It doesn't make sense to have a third table referencing these two,
>> does it?
>
> Not with that relationship. From the names of the entities I
> thought that this should be MANY:MANY (farms as consumers of
> suppliers' products).
>
> But do think about that PK on farm. It should be FARM_CODE on its
> own and SUPP_CODE should be a FK to SUPPLIER. (This smells like
> inherited Paradox hierarchical keys....) The two fields should be
> constrained to be unique - as a UNIQUE constraint if you need to
> refer to the combination as a key; otherwise a unique index would
> suffice.
>
> ./heLen
>