locked
Database design RRS feed

  • Question

  • Say I have a table that contains information about servers. As new servers come online, this table is populated with information about the servers. Now, assume that the server name is unique, and server serial number is unique.

    Scenario #1:

    Server Table
    server_name text,
    server_serial text,
    server_os integer (FK points to os table),
    os_version integer (FK points to os_version table),
    cpu_type integer (FK points to cpu_type table), 

    Now, let's assume there are 20 things that will not be unique about this server (e.g., server_os is not unique), thus in Scenario #1 we have 20 columns that contain FKs pointing to PKs of other tables.

    Scenario #2:

    No normalization. No other tables, just a server table with all the columns related to the server. No FK/PK relationships.

    Normally I would go for Scenario #1. However, in scenario #1 I end up with a lot more queries. For scenario #2, I would simply do an insert for each server. In scenario #1, I would need to do a select to grab the PKs for all 20 columns, and then do my insert with the returned values (21 queries assuming all values exist in the 20 other columns). It gets even worse if there's a new server_os, cpu_type, etc

    Monday, January 5, 2015 4:46 PM

Answers

  • It depends on few factors like whether table will have high frequency DML (insert/update/delete) happening or would be it mostly data retrieval (select) that happens

    I would assume it would be the latter as its storing details on servers which would not growing much rapidly. Also most cases I would presume you retrieving details rather than keeping on adding new server details. As such I would go for #2 approach as its simple and would make select queries single and faster compared to #1 where you will have 20 joins to 20 other tables to get the related info.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 5, 2015 4:51 PM
  • I agree with the answer. I think in this particular scenario over-normalization is not needed and you can keep the information in one table as long as you're consistent. If only one person is updating this information and there are just few servers, I would keep the information in one table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 5, 2015 5:05 PM
    Answerer
  • Remember you don't need to use artificial keys.  There's noting wrong with inserting the actual data in the main table, and still having that be a FK to constrain the values.

    eg

    server_name text,
    server_serial text,
    server_os text  (FK points to os table),
    os_version text (FK points to os_version table),
    cpu_type text (FK points to cpu_type table), 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 5, 2015 6:23 PM

All replies

  • It depends on few factors like whether table will have high frequency DML (insert/update/delete) happening or would be it mostly data retrieval (select) that happens

    I would assume it would be the latter as its storing details on servers which would not growing much rapidly. Also most cases I would presume you retrieving details rather than keeping on adding new server details. As such I would go for #2 approach as its simple and would make select queries single and faster compared to #1 where you will have 20 joins to 20 other tables to get the related info.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 5, 2015 4:51 PM
  • I agree with the answer. I think in this particular scenario over-normalization is not needed and you can keep the information in one table as long as you're consistent. If only one person is updating this information and there are just few servers, I would keep the information in one table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 5, 2015 5:05 PM
    Answerer
  • Hello , 

    In OLTP database ,table need to be normalized but in the context of OLAP , it can be de-normalized


    • Edited by PAPIAS53 Monday, January 5, 2015 5:51 PM
    Monday, January 5, 2015 5:50 PM
  • Remember you don't need to use artificial keys.  There's noting wrong with inserting the actual data in the main table, and still having that be a FK to constrain the values.

    eg

    server_name text,
    server_serial text,
    server_os text  (FK points to os table),
    os_version text (FK points to os_version table),
    cpu_type text (FK points to cpu_type table), 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 5, 2015 6:23 PM