Re: Foreign Keys as first class citizens at design time?

On 08/13/2015 05:40 PM, Stephen Feyrer wrote:
> On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)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(dot)List(at)example(dot)com"},
>>> {"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova(at)example(dot)com"},
>>>
>>> {"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional(at)transaction(dot)org"},
>>>
>>> {"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam(at)lovesspam(dot)com"},
>>>
>>> {"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational(at)transaction(dot)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'

So what if you want to use a different alias?

What if you only wanted the contacts email and not the vendors?

I see the example below, but now you are changing direction for what I
consider no good reason.

>
> Or to follow a reverse semantic:
>
> SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
> join)

The problem that I see is the current method is self-documenting whereas
implicit joins means you have to 'know' what is implied. This means some
other mechanism to discover what is implied. Seems more complicated then
the present situation.

>
> 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.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com