Professional Web Applications Themes

mysql database design normalization question - MySQL

I've got a database to design that will be tracking system information such as hostid, vendor, serial number, etc., most of which I can keep in one table because it is data unique to each system (not vendor, obviously.) Call this table "main." In addition there are about 30 system "checks" I have to add which will be pass/fail values, with a text note to accompany if the check is flagged as a failure. Question is, should I include all 30 checks as cols in the "main" table, or create a separate table to track them? In addition, if any ...

  1. #1

    Default mysql database design normalization question

    I've got a database to design that will be tracking system information
    such as hostid, vendor, serial number, etc., most of which I can keep
    in one table because it is data unique to each system (not vendor,
    obviously.) Call this table "main." In addition there are about 30
    system "checks" I have to add which will be pass/fail values, with a
    text note to accompany if the check is flagged as a failure.

    Question is, should I include all 30 checks as cols in the "main"
    table, or create a separate table to track them? In addition, if any
    check on a specific host fails, I have to log the failure. Should I
    have a table for each type of check that would log the main.id and the
    failure message, or should I have only one table that logs the
    main.id, the check type, and the failure message? It seems to me that
    the multiple tables method has less redundant data.

    manunderstress Guest

  2. #2

    Default Re: mysql database design normalization question

    manunderstress wrote: 

    Put your checks in a second table with columns main_id, check_id and
    failure message. It will be much easier if you ever have to change your
    procedure, i.e. add more checks.

    Multiple tables are redundant in this case.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: mysql database design normalization question

    The definition of the 3rd normal form is ..... each attribute must be
    dependent on the primary key and each attribute should be updatatable
    without conflicting with other attributes of the same table .... so help you
    codd.

    That said, I would create the following design ....
    <host> table
    host_id
    host_type (windows, linux, AIX etc)
    host_vendor
    host_serial_number
    etc.

    <check_master>
    check_id
    check_short_desc
    check_long_desc

    <host_check> -- resolves many-to-many relationship between host and
    check_master... This allows more than 30 checks and a variable number of
    checks per host, depending on the host type.
    hc_id
    hc_host_id
    hc_check_id
    hc_status
    hc_last_upd_dtim
    hc_last_upd_message (denormalization - you can get this from the
    latest host_check_log entry)

    <host_check_log>
    hcl_id
    hcl_hc_id (foreign key)
    hcl_message
    hcl_cre_dtim

    HTH

    "manunderstress" <com> wrote in message
    news:googlegroups.com... 


    Rajesh Guest

Similar Threads

  1. database design and asp question
    By Michael in forum Dreamweaver AppDev
    Replies: 2
    Last Post: June 27th, 09:23 AM
  2. MySQL Database design for placing service orders
    By Chris Morley in forum PHP Development
    Replies: 2
    Last Post: October 20th, 08:01 PM
  3. Database Design Question !!
    By Adam Knight in forum ASP Database
    Replies: 3
    Last Post: August 14th, 08:45 AM
  4. Newbie Database table design question (guid)
    By William in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: August 1st, 10:35 PM
  5. Database design/optimization question
    By AMIT in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 07:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139