share_holder_database-queries.txt

create table if not exists share_holder (
	holder_id serial unique not null,
	holder_name varchar not null,
	created_at timestamptz not null default now(),
	primary key(holder_name,holder_id)
);

create table if not exists stock (
	stock_id serial unique not null,
	stock_name varchar not null,
	stock_num_share varchar not null,
	stock_url varchar not null,
	stock_data_url varchar not null,
	stock_base_volume varchar not null,
	stock_floating float not null,
	created_at timestamptz not null default now(),
	primary key (stock_name,stock_id)
);

create table if not exists holding (
	stock_id int not null,
	foreign key (stock_id) references stock(stock_id),
	holder_id int not null,
	foreign key (holder_id) references share_holder (holder_id),
	created_at timestamptz not null default now(),
	legal_purchase_volume varchar,
	legal_purchase_percent float,
	legal_sale_volume varchar,
	legal_sale_percent float,
	group_pe float,
	pe float,
	eps int,
	closing_price_percent float,
	last_price_percent float,
	last_price int,
	closing_price int,
	month_average_volume varchar,
	market_value varchar,
	transaction_num int,
	transaction_value varchar,
	transaction_volume varchar,
	day_high int,
	day_low int,
	year_high int,
	year_low int,
	buy_or_sell_volume int not null,
	total_volume int not null,
	primary key (stock_id,holder_id,created_at,buy_or_sell_volume,total_volume)
)