Monday, 19 August 2013

MySQL Normalize or Denormalize

MySQL Normalize or Denormalize

I'm building a PHP app to prefill third party PDF account forms with
client data, and am getting stuck on the database design.
The current form has about 70 fields, which seems like far too many to set
up as individual columns, especially as some (ie company/trust
information) are not relevant depending on the type of account the client
requires.
I've tried to normalize but it seems like there would be a lot of joins,
and also require several sub queries for things like multiple addresses.
It also means a ton of extra queries to check if rows exist or not when
updating to decide if the script needs to do an INSERT, a DELETE or an
UPDATE, whereas if it was all in one row, it would basically just be an
UPDATE each time.
Not sure if this helps but here is a list of most of the fields:
id, account_type, account_phone, account_email, account_designation,
account_adviser, account_source, account_complete,
account_residential_unit_number, account_residential_street_number,
account_residential_street_name, account_residential_street_type,
account_residential_suburb, account_residential_state,
account_residential_postcode, account_postal_unit_number,
account_postal_street_number, account_postal_street_name,
account_postal_street_type, account_postal_suburb, account_postal_state,
account_postal_postcode, individual_1_title, individual_1_firstname,
individual_1_middlename, individual_1_lastname, individual_1_dob,
individual_1_occupation, individual_1_email, individual_1_phone,
individual_1_unit_number, individual_1_street_number,
individual_1_street_name, individual_1_street_type, individual_1_suburb,
individual_1_state, individual_1_postcode, individual_2_title,
individual_2_firstname, individual_2_middlename, individual_2_lastname,
individual_2_dob, individual_2_occupation, individual_2_email,
individual_2_phone, individual_2_unit_number, individual_2_street_number,
individual_2_street_name, individual_2_street_type, individual_2_suburb,
individual_2_state, individual_2_postcode, company_name, company_date,
company_unit_number, company_street_number, company_street_name,
company_street_type, company_suburb, company_state, company_postcode,
trust_name, trust_date, settlement_bank, settlement_account,
settlement_bsb
The most this will need to handle is around 200,000 applications, and once
the data is in the database, it won't change very often, if at all - not
sure if that is relevant?
So really just wanted to figure out the smartest way to do design this,
even if it's just a name or topic to research further.

No comments:

Post a Comment