database schema
Trying to come up with a database schema for a bank demo. Here are the entities and attributes: Entity or Attribute Type Size Scale Null Precision Allowed CUSTOMER cust_id serial n/a n/a No fname char 20 n/a Yes mname char 20 n/a No lname char 30 n/a Yes address char 80 n/a Yes city char 20 n/a Yes state char 2 n/a Yes zip char 5 n/a Yes phone_num_home char 12 n/a Yes phone_num_work char 12 n/a No phone_num_mobile char 12 n/a No fax_num char 12 n/a No pager char 12 n/a No email char 20 n/a No gender char 1 n/a No birthdate char 20 n/a No marital_status char 20 n/a No housing_status char 30 n/a No employer char 30 n/a No job_title char 20 n/a No gross_monthly_income money 6 2 No date_of_hire char 20 n/a No source_other_income char 20 n/a No amount_other_income money 6 2 No ACCOUNTS cust_id serial n/a n/a No savings money 10 2 Yes checking money 10 2 Yes loan money 10 2 Yes ira money 10 2 Yes cd money 10 2 No RATES savings numeric 3 2 No checking numeric 3 2 No loan numeric 3 2 No LOAN_TYPE loan_type_code serial n/a n/a No new_auto char 10 n/a No used_auto char 10 n/a No appliance char 10 n/a No signature char 10 n/a No debt_consolidation char 10 n/a No personal_credit_line char 10 n/a No biz_credit_line char 10 n/a No visa char 10 n/a No mastercard char 10 n/a No overdraft char 10 n/a No new_luxury char 10 n/a No used_luxury char 10 n/a No share_secured char 10 n/a No home_equity_loc char 10 n/a No home_equity char 10 n/a No home_improve char 10 n/a No BANK bank_code serial n/a n/a No bank_name char 30 n/a No address char 30 n/a No city char 20 n/a No state char 2 n/a No zip char 5 n/a No I also need to incorporate a co-applicant table which is bascially the same as the customer table. How can I do this most efficiently without causing reduancy and overhead? Feel free to email a revised schema. This would be a great help!! Thanks. Brian Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
Big question. Here are some hints: Tables: Application applic_id serial applic_datetime datetime year to minute type enum (in-person, telephone, mail, email, ...) customer? boolean cust_id integer references (customer.cust_id) ... primary key ( applic_id ) applicant applic_id integer references (application.applic_id) applic_seq smallint ss_no_pers_id_no char(20) fname ... ... primary key( applic_id, applic_seq ) customer cust_id serial applic_id integer references (application.applic_id) open_date date ... primary key ( cust_id ) accounts account_id serial cust_id integer references (customer.cust_id) account_type account_sequence ... primary key ( account_id ) wheels123@my-deja.com wrote: > > Trying to come up with a database schema for a bank demo. Here are the > entities and attributes: > > Entity or Attribute Type Size Scale Null > Precision Allowed > CUSTOMER > cust_id serial n/a n/a No > fname char 20 n/a Yes > mname char 20 n/a No > lname char 30 n/a Yes > address char 80 n/a Yes > city char 20 n/a Yes > state char 2 n/a Yes > zip char 5 n/a Yes > phone_num_home char 12 n/a Yes > phone_num_work char 12 n/a No > phone_num_mobile char 12 n/a No > fax_num char 12 n/a No > pager char 12 n/a No > email char 20 n/a No > gender char 1 n/a No > birthdate char 20 n/a No > marital_status char 20 n/a No > housing_status char 30 n/a No > employer char 30 n/a No > job_title char 20 n/a No > gross_monthly_income money 6 2 No > date_of_hire char 20 n/a No > source_other_income char 20 n/a No > amount_other_income money 6 2 No > > ACCOUNTS > cust_id serial n/a n/a No > savings money 10 2 Yes > checking money 10 2 Yes > loan money 10 2 Yes > ira money 10 2 Yes > cd money 10 2 No > > RATES > savings numeric 3 2 No > checking numeric 3 2 No > loan numeric 3 2 No > > LOAN_TYPE > loan_type_code serial n/a n/a No > new_auto char 10 n/a No > used_auto char 10 n/a No > appliance char 10 n/a No > signature char 10 n/a No > debt_consolidation char 10 n/a No > personal_credit_line char 10 n/a No > biz_credit_line char 10 n/a No > visa char 10 n/a No > mastercard char 10 n/a No > overdraft char 10 n/a No > new_luxury char 10 n/a No > used_luxury char 10 n/a No > share_secured char 10 n/a No > home_equity_loc char 10 n/a No > home_equity char 10 n/a No > home_improve char 10 n/a No > > BANK > bank_code serial n/a n/a No > bank_name char 30 n/a No > address char 30 n/a No > city char 20 n/a No > state char 2 n/a No > zip char 5 n/a No > > I also need to incorporate a co-applicant table which is bascially the > same as the customer table. How can I do this most efficiently without > causing reduancy and overhead? > > Feel free to email a revised schema. This would be a great help!! > Thanks. Brian > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.