AVbUfjUt

1drop database if exists sandbox2;
2create database sandbox2;
3use  sandbox2;
4
5create table `User`(
6	id bigint unsigned not null auto_increment primary key,
7	user_id bigint unsigned not null unique
8	) row_format=compressed;
9
10create table `Account`(
11	id bigint unsigned not null auto_increment primary key,
12	user_id bigint unsigned not null,
13	account_number smallint not null,
14	unique (user_id, account_number),
15	balance decimal (16,2) not null
16	) row_format=compressed;
17
18alter table `Account` add constraint `fk_account_user_id_User` foreign key (user_id) references `User`(user_id) on delete cascade;
19
20create table `Transaction`(
21	id bigint unsigned not null auto_increment primary key,
22	user_id bigint unsigned not null,
23	account_id bigint unsigned not null,
24	account_number smallint not null, # denormalized :( meh
25	initiated_at datetime not null default now(),
26	amount decimal(16,2) not null
27	) row_format=compressed;
28
29alter table `Transaction` add constraint `fk_transaction_user_id_User` foreign key (user_id) references `User`(user_id) on delete cascade;
30alter table `Transaction` add constraint `fk_transaction_account_id_account` foreign key (account_id) references `Account`(id) on delete cascade;
31
32/*
331. Will this shard?
342. Is the below description accurate, will it work within vitess with some vitess'ing?
35description: The goal is to shard by user_id and have a range of those user_ids in some number of shards ~ 5-7. Each table with a user_id in a given range
36would live in the same shard. I know I will have to find the vitess way of things for the auto_incremented id columns but since this is within
37a shard the foreign key relationships should work, no?
38*/