Open-AudIT
https://www.open-audit.org/phpBB3/

Database Schema
https://www.open-audit.org/phpBB3/viewtopic.php?f=5&t=3169
Page 1 of 1

Author:  Mark [ Fri Feb 20, 2009 8:54 am ]
Post subject:  Database Schema

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  |      |
+----------------------------+------------------+------+-----+-------------+------+


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  |      |
+------------------+------------------+------+-----+-------------+------+


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 ?

Author:  Chad [ Fri Feb 20, 2009 9:35 am ]
Post subject:  Re: Database Schema

I'd vote for keeping them separate. It's easier to find what you need that way. Putting it all in a single table would probably make it messy in terms of making changes to it in the future or the ease of which people would be able to modify it to suit their needs.

Just my two cents.

Author:  jbsclm [ Fri Feb 20, 2009 6:24 pm ]
Post subject:  Re: Database Schema

I agree with the previous comment, one table would make it harder to understand. Also putting it all in one (large) table might impact performance for those queries where a table scan is necessary. If you are looking for a hardware serial number you don't want to search records which are only software or services records, I know serial number would probably be indexed, but this is just an example.

Author:  A_Hull [ Sun Feb 22, 2009 10:01 pm ]
Post subject:  Re: Database Schema

Quote:
I will know what is what as I am writing the code, but I am concerned with maintainability and readability for other developers.


... and we will know what it is 'cos that's what all of the well defined comments in the code and the highly readable documentation are for :twisted:

I think the arrangement you have chosen is better than the alternative for a number of reasons, not the least of them being readability. :D

Author:  antorz [ Mon Feb 23, 2009 6:04 am ]
Post subject:  Re: Database Schema

Definitely the first!

Page 1 of 1 All times are UTC + 10 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/