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@xxxxxxxxxxx"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@xxxxxxxxxxx"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@xxxxxxxxxxxxxxx"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@xxxxxxxxxxxxx"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@xxxxxxxxxxxxxxx"}}
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.
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@xxxxxxxxxxx"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@xxxxxxxxxxx"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@xxxxxxxxxxxxxxx"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@xxxxxxxxxxxxx"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@xxxxxxxxxxxxxxx"}}
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
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.
--
Kind regards
Stephen Feyrer
Stephen Feyrer
