Re: [Sqlgrey-users] Add primary keys to tables?

[incredibleh0lg <lis...@li...>]

Hi.


> Le 09/02/2016 00:14, Lionel Bouton a écrit :
>> Hi,
>> 
>> I didn't answer the original message at first because :
>> - I believed I reacted on a similar subject several years ago,
>> - the message title doesn't actually make sense to me.
>> 
>> I suspect some people are coming here with a basic ORM background and
>> don't fully understand the difference between primary keys and object
>> ids (PKs/OIDs in the following). So here is a complete answer..
> 
> If people want to dig more OIDs are in fact surrogate keys (by
> opposition to natural keys which are used in SQLgrey). There's a ton of
> documentation/discussion on the subject.

I understand that this can bring up discussions. But I would disagree with the term OIDs (and thanks for the kind words about my “basic" ORM background...). Basically one can choose between Natural Keys and Generated / Artificial Keys. In my experience natural keys are always not the best choice. In every real world commercial project you will end up in changing your keys, because the natural candidates that are so unchangeable, do change. So generic keys are - at least for me - always the safest way to go.
One can still use generic primary keys and define constraints that the combination of local part and domain have to be unique.

Regarding the speed part: It is a completely valid point to decide to not use real primary keys to improve the performance. But in a normal database design you can achieve this by having views or pre-aggregated tables - or use one of the NoSQL databases.

Another point made in this discussion was to give a use case: Third party applications using the data in the database. In my case, I’d like to provide a similar functionality as SQLgrey Webinterface provides. So present the user with relevant entries - e.g. in the connect table - and let him decide what to do with it. In the current format this means, that the request has to carry the WHOLE entry as parameter to get it moved or deleted. This is pretty wasteful and it is the reason why I asked if somebody tested a slightly adjusted schema where generated primary keys are added by the database.In theory, this shouldn’t break the application.

Last but not least, the argument with “wasted space” is insignificant today. The only concern I have about this is that this can be an issue for servers with huge traffic, as the keys can get pretty big quite quickly. So having this as an optional feature would be the best.

As there is no real further development, it is maybe a good exercise to reimplement it with support for NoSQL databases (which, btw, use generic keys too). 
But thanks for the feedback anyway - sqlgrey is a really reliable tool which is doing great work for years now. So please do not see this is critic, it was more a feature request / question about experiences :)

Cheers,
Holger