Project

General

Profile

Help required in dbo Many to one relationship table creation.

Added by Rathnadhar K V almost 5 years ago

Namaskara,

Please help.

I am writing a program for financial analysis of stocks in equity market.

I need to establish many to one relationship between 2 tables. They are :

1. company_info_tbl

  1. daily_stock_price_tbl

company_info_tbl has the symbol information and market cap information.

class company_info

{

public:

std::string symbol;

int market_cap; //value in millions.

}

The daily_stock_price_tbl has the daily equity price for each symbol in company_info_tbl.

class daily_stock_price_tbl

{

public:

std::string symbol

float closing_price;

}

In daily_stock_price_tbl i should have foreign key for symbol column that references symbol column in company_info_tbl.

Therefore I add the following dbo information:

class company_info

{

public:

STD_STRING symbol;

int market_cap;

dbo::collection<dbo::ptr> daily_stock_prices;

company_info();

template

void persist(Action& action)

{

Wt::Dbo::field(action,symbol,"symbol");

Wt::Dbo::field(action,market_cap,"market_cap");

dbo::id(action, symbol, "symbol_id", 20);

dbo::hasMany(action, daily_stock_prices, dbo::ManyToOne, "company_info");

}

};

class daily_stock_price

{

public:

dbo::ptr company_info_record;

STD_STRING symbol;

float closing_price;

daily_stock_price();

template

void persist(Action& action)

{

Wt::Dbo::field(action,symbol,"symbol");

Wt::Dbo::field(action,closing_price,"closing_price");

dbo::id(action, symbol, "symbol_id", 20);

dbo::belongsTo(action, company_info_record, "company_info");

}

};

When it creates the database, I get the following additional field:

Additional information:

1. OS: OpenSuse Linux, Tumbleweed.

  1. Database: Maria DB (Mysql).
  2. 64 bits architecture.

My requirement is :

1. To have symbol in company_info table as primary key.

  1. To have symbol in company_info table as foreign key for symbol in daily_stock_price table.
  2. Not to have additional fields (text fields) as they gobble up huge space as the table grows.
  3. Not to have id column as it serves no purpose in either tables, symbol should be primary key.

Please let me know what code modifications I should do.

Thanks

Regards

Rathnadhar K V