database design - MySQL Normalize or Denormalize -


i'm building php app prefill third party pdf account forms client data, , getting stuck on database design.

the current form has 70 fields, seems far many set individual columns, (ie company/trust information) not relevant depending on type of account client requires.

i've tried normalize seems there lot of joins, , require several sub queries things multiple addresses.

it means ton of queries check if rows exist or not when updating decide if script needs insert, delete or update, whereas if in 1 row, update each time.

not sure if helps here list of of 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 need handle around 200,000 applications, , once data in database, won't change often, if @ - not sure if relevant?

so wanted figure out smartest way design this, if it's name or topic research further.

generally speaking can divide database 2 broad categories:

  1. oltp systems

    online transaction processing systems write intensive i.e. lot of updates compared reads of data. system typically day day application used business users of scopes i.e. data capture, admin etc. these databases normalized extreme , demoralized performance gains in areas.

  2. olap/dss system:

    on line analytic processing database large data warehouse systems. used support analytic activities such data mining, data cubes etc. typically information used more limited set of users oltp. these database denormalised.

go read here short description of these , main differences. oltp vs olap

regarding insert/update/delete point go read mysql on duplicate key update statement resolve issue easily. called merge operation in database systems.

now dont understand why worried joins. have had tables millions (500 000 000+) rows joined other tables large in size , queries ran fast. designing database eliminate joins not idea.

my suggestion is:

if designing oltp system normalise as possible denormalise increase performance needed. olap system @ star schemas etc , dont bother normalizing first. oh way of olap systems use oltp system data source.


Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -