Re: Foreign Keys as first class citizens at design time?
От | Adrian Klaver |
---|---|
Тема | Re: Foreign Keys as first class citizens at design time? |
Дата | |
Msg-id | 55CD3D35.9080203@aklaver.com обсуждение исходный текст |
Ответ на | Re: Foreign Keys as first class citizens at design time? ("Stephen Feyrer" <stephen.feyrer@btinternet.com>) |
Ответы |
Re: Foreign Keys as first class citizens at design time?
("Stephen Feyrer" <stephen.feyrer@btinternet.com>)
|
Список | pgsql-general |
On 08/13/2015 05:40 PM, Stephen Feyrer wrote: > On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver > <adrian.klaver@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.List@example.com"}, >>> {"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@example.com"}, >>> >>> {"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@transaction.org"}, >>> >>> {"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@lovesspam.com"}, >>> >>> {"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@transaction.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.klaver@aklaver.com
В списке pgsql-general по дате отправления: