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 wrote:
> 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.
>

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.
jstucklex@attglobal.net
==================

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" <chadw3@gmail.com> wrote in message
news:1173391324.449717.183850@8g2000cwh.googlegrou ps.com...
> 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.
>