In the DB I have (currently) 24 tables containing details about hardware. Each table relates to a specific component. Think battery, bios, floppy, hard drive, memory, modem, etc, etc. Most of these tables have very similar columns. Every table has (usually) id, system id, timestamp, first timestamp, model, manufacturer, serial, description. Most tables then also have between 0 and 6 additional fields specific to that type of hardware. An example below for the "processor" table...
[code]+----------------------------+------------------+------+-----+-------------+------+ | Field | Type | Null | Key | Default | Extra| +----------------------------+------------------+------+-----+-------------+------+ | processor_id | int(10) unsigned | NO | PRI | NULL |auto_inc| | system_id | int(10) unsigned | YES | MUL | NULL | | | processor_cores | int(2) unsigned | NO | | 0 | | | processor_description | varchar(100) | NO | | | | | processor_speed | int(10) unsigned | NO | | 0 | | | processor_manufacturer | varchar(100) | NO | | | | | processor_power_management | varchar(20) | NO | | | | | timestamp | datetime | NO | | 0000-00-00 | | | first_timestamp | datetime | NO | | 0000-00-00 | | +----------------------------+------------------+------+-----+-------------+------+[/code]
So, my question is - am I better off combining the 24 hardware tables into a single table ? I would need the core fields, along with some "generic" columns for specific data and a "type" column. I'd also use a "parent id" column - some things depend on others. Think partitions -> disks. Or IP details -> network card. SCSI device -> SCSI Card.
Obviously this would reduce the number of tables and simplify the DB. What I am concerned about is the "generic" columns. They will reduce "readability" of the DB schema. How do you know that generic_1 is the number of processor cores when the row is of processor type, but is also the memory_bank_number when the row is of memory type ?
[code]+------------------+------------------+------+-----+-------------+------+ | Field | Type | Null | Key | Default | Extra| +------------------+------------------+------+-----+-------------+------+ | id | int(10) unsigned | NO | PRI | NULL |auto_inc| | system_id | int(10) unsigned | YES | MUL | NULL | | | parent_id | int(10) unsigned | YES | | | | | type | varchar(100) | NO | | | | | description | varchar(100) | NO | | | | | model | varchar(100) | NO | | | | | manufacturer | varchar(100) | NO | | | | | serial | varchar(100) | NO | | | | | generic_1 | varchar(100) | NO | | | | | generic_2 | varchar(100) | NO | | | | | generic_3 | varchar(100) | NO | | | | | generic_4 | varchar(100) | NO | | | | | generic_5 | varchar(100) | NO | | | | | generic_6 | varchar(100) | NO | | | | | timestamp | datetime | NO | | 0000-00-00 | | | first_timestamp | datetime | NO | | 0000-00-00 | | +------------------+------------------+------+-----+-------------+------+[/code]
I will know what is what as I am writing the code, but I am concerned with maintainability and readability for other developers.
Or - do I put in additional columns with the same names as the columns from the original tables. This would make the number of columns increase a lot and I'm not sure it's worth it..
Thoughts ? Combine tables and reduce readability, or keep them seperate ?
_________________ Support and Development hours available from [url=https://opmantek.com]Opmantek[/url]. Please consider a purchase to help make Open-AudIT better for everyone.
|