Created
December 6, 2018 10:19
-
-
Save gigatexal/b8a39db91a3fa27c9e905192c8b338d4 to your computer and use it in GitHub Desktop.
sql scheme before vitess'ing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop database if exists sandbox2; | |
create database sandbox2; | |
use sandbox2; | |
create table `User`( | |
id bigint unsigned not null auto_increment primary key, | |
user_id bigint unsigned not null unique | |
) row_format=compressed; | |
create table `Account`( | |
id bigint unsigned not null auto_increment primary key, | |
user_id bigint unsigned not null, | |
account_number smallint not null, | |
unique (user_id, account_number), | |
balance decimal (16,2) not null | |
) row_format=compressed; | |
alter table `Account` add constraint `fk_account_user_id_User` foreign key (user_id) references `User`(user_id) on delete cascade; | |
create table `Transaction`( | |
id bigint unsigned not null auto_increment primary key, | |
user_id bigint unsigned not null, | |
account_id bigint unsigned not null, | |
account_number smallint not null, # denormalized :( meh | |
initiated_at datetime not null default now(), | |
amount decimal(16,2) not null | |
) row_format=compressed; | |
alter table `Transaction` add constraint `fk_transaction_user_id_User` foreign key (user_id) references `User`(user_id) on delete cascade; | |
alter table `Transaction` add constraint `fk_transaction_account_id_account` foreign key (account_id) references `Account`(id) on delete cascade; | |
/* | |
1. Will this shard? | |
2. Is the below description accurate, will it work within vitess with some vitess'ing? | |
description: 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 | |
would 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 | |
a shard the foreign key relationships should work, no? | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment