0

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure.

Requirement:

  • Expecting around millions of users in future.
  • I have to use unique user id on other systems also, may be on MongoDB, redis etc.

Approach:

  • I am using pseudo_encrypt() as unique user_id (BIGINT or BIGSERIAL), so that no one can guess other ids. For example: 3898573529235304961
  • Using user_id as foreign key in another table. I am not using primary key of user table as foreign key.

sample user table & publisher table schema

Any suggestions?

  1. Use of unique key as foreign key everywhere in other tables, am I doing it correct?
  2. Any performance issue during CRUD operations & with complex joins?
  3. Use of unique key in any other database is correct way? (in case of distributed environment)
Community
  • 1
  • 1
Amreesh Tyagi
  • 1,378
  • 15
  • 15
  • if you don't get any traction here, consider dba.stackexchange.com – Baronz Mar 12 '16 at 18:52
  • Consider [xtea](https://wiki.postgresql.org/wiki/XTEA_%28crypt_64_bits%29) instead `pseudo_encrypt()` for unguessable 64 bits keys. – Daniel Vérité Mar 12 '16 at 19:45
  • If users can be accessed by a guessed ID then you have a problem – paparazzo Mar 13 '16 at 04:20
  • There are security measures to authorize users to prevent access with guessed Ids, which is not the part of this post. If we expose/use any 3rd party service, it is necessary to expose unique key to consumer in REST world. For example,try to use any API on https://developers.google.com/oauthplayground/ , you can see https://www.googleapis.com/oauth2/v2/userinfo is returning user info object similar to this response {"name": "AMREESH", "id": "117344186683399370163" }. I am talking about this Id. – Amreesh Tyagi Mar 13 '16 at 04:32
  • 1
    Why you need both `id` and `user_id` in table `User`? Use only `user_id`. – Renzo Mar 13 '16 at 05:33
  • I would handle encryption/decryption of the id in my API or stored procs rather than recording the encrypted value. Otherwise, the design looks fine and there's no problem with using a non-primary unique key as the target of a foreign key constraint. – reaanb Mar 14 '16 at 06:23

1 Answers1

0

You are wading into flame war territory here over the question of natural vs surrogate primary keys. I agree with you and often use unique keys as foreign keys, and designate natural primary keys as such. On PostgreSQL this is safe (on MySQL or MS SQL it would be a bad habit though).

In PostgreSQL the only differences between primary keys and unique constraints are:

  1. A table can have only one primary key
  2. primary keys are not null on all columns

In practice, if you have a table defined as NOT NULL UNIQUE, it is just about the same as a single primary key.

On other dbs, often times table structure is optimized for primary key lookups which is why this is a problem, and there may be tools that don't like it but those are questions outside the realm of db design per se.

You are better to use normal serials and have real access controls than try to build things on obscurity. The obscurity controls are likely to perform worse, and be less secure than just doing things right however.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • Thanks Chris. I am going to use serial. Would it be ok, if I increase it by some random value like autoincrement 457 in mysql? In this case ids would be like 1, 458, 915. These ids would be non-guessable. Although it will increase page size of my db. Suggestion? – Amreesh Tyagi Mar 14 '16 at 23:17