student database structure
Posted: Wed Jan 18, 2006 11:47 am
hi,
i'm modifying an old database which is running very slow because of its structure.
i'm looking for some advice on structuring the database for over coming speed issues.
heres the scenario:
a country has many territories and designated to one region
a territory is owned by a principal. a principal might own more than one territory
a school opens in a territory, always offering 3 subjects, which are always the same in all countries
students join any school in any territory
the school always offers a full paying, half scholarship or full scholarship product
if two brothers are attending a school owned by the same principal, the youngest student always gets a 50% discount and the oldest pays the full fee
how can i capture all this information in a well designed relational database that stores history invoices
any help on designing a database with families linked to students and creating invoices will be a great help
i have had a go but correct me if i'm wrong
tbl_country (cid, c_name)
tbl_location (lid, l_name, frn_cid, frn_rid)
tbl_region (rid, r_name, frn_cid)
tbl_school ( scid, sc_name, frn_lid,frn_tid)
tbl_term (tid,t_name)
tbl_member (mid,frn_fmid,frn_scid)
tbl_family_member (fmid, fm_fname, fm_lname, frn_faid)
tbl_family (faid, fa_name, fa_adr)
tbl_orders(orid, orid_datein,or_status,frn_faid)
tbl_order_items (oid,o_unit_price,frn_orid,frn_fmid,pid,frn_scid,frn_lid,frn_tid) // this will store current and old invoice data
tbl_order_payment_made (opid,op_datein,op_amount,frn_orid)
tbl_product (pid,p_name,p_code)
i'm modifying an old database which is running very slow because of its structure.
i'm looking for some advice on structuring the database for over coming speed issues.
heres the scenario:
a country has many territories and designated to one region
a territory is owned by a principal. a principal might own more than one territory
a school opens in a territory, always offering 3 subjects, which are always the same in all countries
students join any school in any territory
the school always offers a full paying, half scholarship or full scholarship product
if two brothers are attending a school owned by the same principal, the youngest student always gets a 50% discount and the oldest pays the full fee
how can i capture all this information in a well designed relational database that stores history invoices
any help on designing a database with families linked to students and creating invoices will be a great help
i have had a go but correct me if i'm wrong
tbl_country (cid, c_name)
tbl_location (lid, l_name, frn_cid, frn_rid)
tbl_region (rid, r_name, frn_cid)
tbl_school ( scid, sc_name, frn_lid,frn_tid)
tbl_term (tid,t_name)
tbl_member (mid,frn_fmid,frn_scid)
tbl_family_member (fmid, fm_fname, fm_lname, frn_faid)
tbl_family (faid, fa_name, fa_adr)
tbl_orders(orid, orid_datein,or_status,frn_faid)
tbl_order_items (oid,o_unit_price,frn_orid,frn_fmid,pid,frn_scid,frn_lid,frn_tid) // this will store current and old invoice data
tbl_order_payment_made (opid,op_datein,op_amount,frn_orid)
tbl_product (pid,p_name,p_code)