I am having following problem:

User fills out the form, which consists of two sets of fields - header (basic information about biological observation) and detail (measurements and advanced information). It is assumed, that not every user will fill out the detailed information.

I am wondering, what is better design with respect to performance

  • to have one table with all fields and those detail fields having NULL values
  • to have two separate tables - one for header and one from detail with 1 to 1 relation, and in case the user did not fill the detail, do not insert record into detail table.

Further information: header and detail will always be used together, so for every SELECT statement, I would need to join them.

I suppose I could formulate the question as follows: In case of two-tables design, would the space I save make up for the slower performance (due to join)?

I am using MySQL database.

  • It depends on what you are going to do with the data. Without this information, we are just guessing about performance. – Walter Mitty Jul 16 '15 at 9:50
  • it is a database to store biological field notes - so there will be a lot of inserts, few updates, close to none deletes. there are likely to be several views to display summary information – Zbynek Jul 16 '15 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.