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)
student database structure
Moderator: General Moderators
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
Do you know entity relationship diagram...learn it if you do not.
First can you make out relationships among entities,tables...relationships may be one to one, one to many and many to many.
It's better off, if you start like this....
You are saying the older db structure was not good and slow...there might be reasons that the tables are not properly indexed..so list the indexes you are going to develop for each table.
edit: one more thing, please make sure that you make your field names more understandable............
Follow consistent naming practice....
First can you make out relationships among entities,tables...relationships may be one to one, one to many and many to many.
It's better off, if you start like this....
You are saying the older db structure was not good and slow...there might be reasons that the tables are not properly indexed..so list the indexes you are going to develop for each table.
edit: one more thing, please make sure that you make your field names more understandable............
Follow consistent naming practice....