locked
Database design help RRS feed

  • Question

  • Good day guys,

    Quick question here.

    I have about 30 fields of information I have to store about each and every user that uses our profile service. Would it be good practice to split the 50 fields into two tables using foreign keys or just one table with all the fields in and which would give the best performance?

    Thanx in advance!
    Later
    Wednesday, October 12, 2011 7:53 PM

Answers

  • The general rule is to normalize database schema as much as you can, then denormalize depending on your needs and your queries.

    Denormalize or normalize decision depends on usage of your tables and your situation. For example if you want to achive more performance and your Users table will allways be used as a single entity (query takes all fields) you can use one table. If a lot of your queries take only 4 columns from the table and several queries use whole table you can use 2 tables (Users and UserDetails) to separate load.

    But if you use 1 table (1 scenario I described) you can create indexes for the queried 4 columns. If you use 2 tables (second scenario) and you create constraints and your queries use joins between theese tables, you will notice that constraints can help to avoid redundant joins (optimizer will understand that you don't need to access the second table). There is a lot of aspects, that you should consider.

    So, I want to say, that you must consider both variants and make decision based on your scenario.

     

    Sorry for my English,

    Denis

    • Marked as answer by Stephanie Lv Wednesday, October 19, 2011 1:06 PM
    Thursday, October 13, 2011 10:48 AM

All replies

  • hi

    if you use all 50 fields in one record you may design one table. if you are not you can split them by refer and design two or more tables.

     


    Regards Ahmad
    Thursday, October 13, 2011 7:23 AM
  • The general rule is to normalize database schema as much as you can, then denormalize depending on your needs and your queries.

    Denormalize or normalize decision depends on usage of your tables and your situation. For example if you want to achive more performance and your Users table will allways be used as a single entity (query takes all fields) you can use one table. If a lot of your queries take only 4 columns from the table and several queries use whole table you can use 2 tables (Users and UserDetails) to separate load.

    But if you use 1 table (1 scenario I described) you can create indexes for the queried 4 columns. If you use 2 tables (second scenario) and you create constraints and your queries use joins between theese tables, you will notice that constraints can help to avoid redundant joins (optimizer will understand that you don't need to access the second table). There is a lot of aspects, that you should consider.

    So, I want to say, that you must consider both variants and make decision based on your scenario.

     

    Sorry for my English,

    Denis

    • Marked as answer by Stephanie Lv Wednesday, October 19, 2011 1:06 PM
    Thursday, October 13, 2011 10:48 AM