Re: [GENERAL] Foreign Keys as first class citizens at design time?
Hi, I agree with each of the points you've made. The idea here is meant as an extension of what is already available. So yes, this is intended to answer the questions of the designer's original model. The consideration being that you design your database and the underlying logic of your decision are already built in. Then when querying that database to perform mundane day to day business tasks your query set is simple and easy to build. I don't see why this should detract from the idea of a free form query being built using the existing tools to answer a new question which may be entirely unrelated to the original models purpose. This just couldn't work independently of the current SQL feature set. Additionally, this something which has not really been touched on, this allows a form of iterative structure in a one line query. On Fri, 14 Aug 2015 03:52:28 +0100, David G. Johnston david.g.johns...@gmail.com wrote: On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer stephen.fey...@btinternet.com wrote: When we design databases, invariably, normally we design the queries at the same time. Well this may be true to an extent well implemented models have the ability to answer questions (queries) the original designer never thought of or that were not important at the time. As for the rest - invest in a good graphical query builder (or write one if the existing choices are insufficient). David J. -- Kind regards Stephen Feyrer
Re: [GENERAL] Foreign Keys as first class citizens at design time?
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
adrian.kla...@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.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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.kla...@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.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign Keys as first class citizens at design time?
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.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-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.
--
Kind regards
Stephen Feyrer
Re: [GENERAL] Foreign Keys as first class citizens at design time?
On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer stephen.fey...@btinternet.com wrote: When we design databases, invariably, normally we design the queries at the same time. Well this may be true to an extent well implemented models have the ability to answer questions (queries) the original designer never thought of or that were not important at the time. As for the rest - invest in a good graphical query builder (or write one if the existing choices are insufficient). David J.
Re: [GENERAL] Foreign Keys as first class citizens at design time?
On Fri, 14 Aug 2015 01:58:29 +0100, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 08/13/2015 05:40 PM, Stephen Feyrer wrote:
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
adrian.kla...@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.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-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
