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

On Fri, 14 Aug 2015 01:58:29 +0100, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:

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

That is a good question, the point of making foreign keys links into
another table is an attempt to reduce verbosity. Admittedly with that
reduction you can lose expressiveness. Given that you would know the
semantics of the naming scheme you could use:

SELECT contacts-email AS Econtacts, vendor-email AS 'Evendor' FROM
profiles WITH-IMPLICIT-JOIN

This syntax is not far removed from the regular syntax anyway. In other
words, if I'd thought to use the tablename.field nomenclature in the first
place you probably wouldn't have asked that question (I think). The
regular method should not magically disappear just because you've got a
new tool in your box of tricks.

SELECT contacts.email AS Econtacts <other fields> AS Econtacts FROM
profies JOIN contacts...

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

As I see it, there are two possible ways this might work example would
give the contacts email only.

SELECT contacts-email FROM profiles WITH-IMPLICIT-JOIN

or

SELECT contacts-email AS Econtacts, vendor-email AS '' FROM profiles
WITH-IMPLICIT-JOIN

I prefer the former example as it is less verbose but retains the
specificity. Some might argue the latter is more readable others that it
is more confusing, I'd say both.

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

No, the reason for the change of direction is that there is a join
happening, in the case of the implicit join the table holding the foreign
key is to the right so it would look like a right join.

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

Once you understand the semantics (should they ever exist beyond this
discussion) of this system it will be self documenting also. You'll be
able to see that a foreign key one table is the primary key in another.
Admittedly this wasn't well represented in my example but that was my
first attempt at describing this to everyone. Also this mechanism easily
addresses at least one example of where a complex iterative function would
otherwise be required.

When we design databases, invariably, normally we design the queries at
the same time. For a well designed database, the queries themselves will
remain static and unchanging for a long time. Yet when we design a
database it is like we do the job twice first normalising the tables to
the best of our abilities and then writing the queries to access those
tables. Often with the best will in the world on a big project it can be
difficult to keep track of how this should relate to that, or why. The
queries are a representation of the relationships that were created at
design time. So why not put some reason and logic behind those
relationships in the tables at design time?

I'm not trying to suggest this thought is a new panacea (even if it is
only for the next five minutes until the next one comes along).

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