Information structure and database implementation

[BlancaCC]

In order to solve #7 we need to structure information, in this first iteration:

The information is structured by:

• id (unique, key, automatic)
• Timestamp first creation (automatic)
• Text area (required, string)
• Generated tag (list of string done by the app)
• Personal tags (list of strings)

What would be the more suitable database option?

• SQL or not SQL?
• Which fields should have the information explained

[BlancaCC]

In order to choose the database I have search many pages:

• how to efficiently choose the right database
• How to Choose The Right Database for Your Application?

The circumstances we need to have in mind are:

1. Integration: A priori we do not have any special requirements. (We have freedom in this sense)
2. Scaling requirements: The amount of data it is suppose to be big. So we will need a database easy to escalate horizontally and ideally transaction rate or throughput.
   To satisfy this requirement son candidates are: Cassandra, MongoDB, Google Cloud Spanner, Amazon RDS (The cloud version of MySQL).
3. Support consideration Security (If we use a NoSQL version we will need to work a bit more)
4. CAP: Consistence, availability and partition tolerance I rather prefer availability and partition tolerance. So some of our options are: Cassandra, Riak, Couched.

Since I do not want cloud services and having in mind all the considerations, the best option is Cassandra

[BlancaCC]

Cassandra data modeling

To sum up, to solve an item of #7 we are going to implement Cassandra DB and for this first iteration we need to implement:

Fields

The data structure needed is:

• card id uuid
• user id uuid
• card (the information) text
• explicit tags SET <text>
• implicit tags SET <text>
• created time timestamp, automatic

Primary key ((id, user_id), user_id) (cluster ordered by user id)

Cassandra characteristics

Come characteristic we have had in mind:

• Casandra Store data in tables
• Use CQL (Cassandra Query Language)
• Does not support relational data modeling!!
• Query-driven modeling Since joins are not supported, all required fields must be grouped together in a single table. (Use data duplication to high read performance)

Casandra have two keys

• id is des to generate partition key.
• c partition key used to sorting within a partition data among the nodes.

Sources:

• Cassandra type official doc
• Cassandra data modeling

[BlancaCC]

Have been done in this commit 6694056d6add8b968087745674789c55a704140c

[BlancaCC]

It is need to restructure the design since set operation are not available for queries.

source min 9

[BlancaCC]

After thinking about the new design it would be:

Cards table

• user_id text unique
• card id uuid
• card (the information) text
• explicit tags text
• implicit tags text
• created time timestamp, automatic

Primary key ((card_id, user), user_id) (cluster ordered by user id)

The tags are words separated by ,comas Filter them by expressions

Users

• user
• password
• mail

[BlancaCC]

For a local storage inside mobile we are going to use a SQL database (SQLite).

Tables

• User information
• Tags
• Cards

User information

• userID TEXT PRIMARY KEY
• userName TEXT NOT NULL
• mail TEXT NOT NULL
• password TEXT NOT NULL

Tags

• tag TEXT NOT NULL
• cardID TEXT NOT NULL PRIMARY KEY (tag, cardID)

Card

• cardID TEXT PRIMARY KEY
• content TEXT NOT NULL

Some docs

• SQLite data types
• composite primary key Sqlite
• SQLite create table

[BlancaCC]

This is not a minimal product at this time