Re: Foreign Keys as first class citizens at design time? - Mailing list pgsql-general

On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

> On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
>> Hi,
>>
>> This is probably not an original question merely one which I haven't
>> been able to find an answer for.
>>
>> Basically, the question is why is there not an equivalent foreign key
>> concept to match the primary key we all already know an love?
>>
>> How this would work, would be that the foreign key field in the host
>> table would in fact simply be a reference to a key field in the guest
>> table. Then in the respective SQL syntax a semantic reference may then
>> be made whether or not to follow such links.
>>
>> Therefore as an example:
>>
>> {system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket
>> List","user-attribute":"Bucket.List@example.com"},
>>
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@example.com"},
>>
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@transaction.org"},
>>
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@lovesspam.com"},
>>
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@transaction.org"}}
>>
>> SELECT directory-name FROM system WITH-IMPLICIT-JOIN
>> WHERE-PK-IS-NOT-LINKED
>>
>> This would yield
>>
>> directory-name    directory-name
>> Bucket List
>> Supernova
>> Transactional     Relational
>> Spam
>>
>> Alternatively linking two user tables - profiles and contacts
>>
>> profiles
>> PK-profiles
>> user-name
>> real-name
>> age
>> gender
>> region
>>
>>
>> contacts
>> PK-contacts
>> FK-profiles
>> phone
>> email
>> icq
>> home-page
>>
>> Getting the user-name and email would look something like:
>>
>> SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
>>
>>
>> When building our databases we already put a lot of work in normalising
>> as much as we can. Then after all that work we have to virtually start
>> again building up select, insert and update statements etc. all with all
>> that referential integrity in mind.
>>
>> The advantages of a first class foreign key field as I see it are at
>> least two fold. One it make building and maintaining your database
>> easier. Two it is a means to provide some iterative structures easily
>> coded.
>>
>> To me this looks like a good idea.
>
> What happens if you have more then one child table with the same field?
>
> So:
>
> contacts
>     FK-profiles
>     ....
>     email
>     ....
>
> vendors
>     FK-profiles
>     ....
>     email
>     ....
>


In that case the result table would look something like:

SELECT email FROM profiles WITH-IMPLICIT-JOIN

'contacts-email','vendors-email'

Or to follow a reverse semantic:

SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)

This would give you
'age','region','email'

Whereas:

For a simple vendors table which might look like:

brand
market
email
rating

SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a right
join)

'brand','region','email'



One point I would like to make clear, is that the foreign key linking
should be a design choice.



--
Kind regards


Stephen Feyrer