0

I am trying to design a database to store user information and to authenticate them.

My fields include Name, email, password, phone number, city.

How should I partition my data so that its efficient to authenticate them??

Julia Anderson
  • 11
  • 4

1 Answers1

1

There are different ways of handling this depending on your needs. We recommend you use a natural key such as email addresses since they are universally unique by default.

Here's a table schema that uses emails as the partition key:

CREATE TABLE users_by_email (
    email text,
    name text,
    password text,
    phone text,
    city text,
    PRIMARY KEY (email)
)

A variation is if you want users to be able to choose a username:

CREATE TABLE users_by_username (
    username text,
    email text,
    name text,
    password text,
    phone text,
    city text,
    PRIMARY KEY (username)
)

We recommend that you use Cassandra's compare-and-set (CAS) feature with lightweight transactions (LWTs) using the conditional IF EXISTS and IF NOT EXISTS. For example, to prevent overwriting details of existing users:

INSERT INTO users_by_email (...) VALUES (...) IF NOT EXISTS

INSERT INTO users_by_username (...) VALUES (...) IF NOT EXISTS

As a side note unrelated to the schema, we recommend for best practice that you encrypt user passwords before storing them in the database. Cheers!

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
  • Is it useful to use email as the partition key?? Wouldn't it create too many partitions?? – Julia Anderson Aug 23 '21 at 13:19
  • Any natural key is useful and email addresses are used a lot in various Cassandra use cases since they are universally unique. You will have as many partitions as you have users. Cheers! – Erick Ramirez Aug 23 '21 at 22:57