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

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

>
>
> --
> Kind regards
>
>
> Stephen Feyrer

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