student database structure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

student database structure

Post by gurjit »

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)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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....
Post Reply