How to Convert MYSQL Polling Database Structure into CrateData Tables

Question

Hi, I just want to convert this Mysql Table structure into crateData tables.

Below is list of tables associated with this Polling App.

1. user (User details)
2. Poll (Poll details)
3. Rating (Rating for a poll)
4. PollComment (Comments belongs to a poll by user)
5. FavouritePoll (List on Favourite Polls by user)
6. user_follow (User on which it has to follow)
7. Category (Category on which Poll belongs)

Thanks

Answer 1

Thanks @Bernd Dorn for your elaborated answer regarding your schema:

@Bernd Dorn: you need to do things a bit different not only for crate but for general performance reasons. so there is no general answer on how to transform the schema. just some point outs here.

in general you should create a blob table for your binary objects like images (see https://crate.io/docs/stable/blob.html#creating-a-table-for-blobs) and reference them via the digest, which is of type string.

data types (see https://crate.io/docs/stable/sql/ddl.html#data-types) : for varchar* use string for datetime use timestamp

proposed structure changes:

use the poll_id as routing value if possible.

in general, try to avoid primary keys which are only there as accessor . crate currently does not support automatic key generation. so the client has to provide those ids. it is hard to generate unique integer values, since you cannot have a incrementing value without shared state. you can a uuid function and string as column type for ids (for example the poll type could be a speaking identifier).

for ratings i would suggest building the primary from the unique values. also use one column for up and down ratings, like this:

create table ratings ( poll_id int primary key, user_id int primary key, rating byte ) clustered by (poll_id);

favoritepoll could be a column on the user of type array(int) referencing the poll - or just int if only one reference is possible

currently joins need to be done on the client side, which is easy for 1:1 and n:1 relations. any other types of joins can be worked around by denormalization, which depends on the queries, that are executed against the data.