Open-AudIT

What's on your network?
It is currently Mon Apr 23, 2018 4:19 am

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Database Schema
PostPosted: Fri Feb 20, 2009 8:54 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1944
Location: Brisbane, Australia
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 ?

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Schema
PostPosted: Fri Feb 20, 2009 9:35 am 
Offline
Contributor

Joined: Fri Jul 04, 2008 6:46 am
Posts: 153
Location: USA - WI
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.

_________________
OA Server: Debian Squeeze w/ Apache2
Auditing: 700 Workstations, 250 or so Retail Terminals, about 75 Servers
OS's: Windows XP/2003/2008/2008 R2/Vista/7, Debian
LDAP: Active Directory 2008 R2


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Schema
PostPosted: Fri Feb 20, 2009 6:24 pm 
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.


Top
  
Reply with quote  
 Post subject: Re: Database Schema
PostPosted: Sun Feb 22, 2009 10:01 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
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

_________________
Andrew

OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Schema
PostPosted: Mon Feb 23, 2009 6:04 am 
Offline
Newbie

Joined: Fri Nov 14, 2008 12:25 am
Posts: 12
Definitely the first!

_________________
OA Server: Windows Server 2003 / IIS6
Auditing: 1200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows 2008 Server
LDAP: Active Directory


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group