Multiple lookups and inserts per row?

[Brad Edwards]

Sorry if this one's a bit lengthy, but I'm not sure how else to describe it. I'm a novice database developer, but I'm pretty sure I know how to get my database into 3rd normal form. I'm fairly certain I need 3rd normal form for my database because of the LARGE amount of data I need to store in a single table. After normalizing the database, that 1 table is nothing but numbers, and I have 3 lookup tables for those values. Here's an example to illustrate the situation:

swconfig_names (a table storing software configuration value names):
id - name
---------
1 - path
2 - version
3 - date_installed

swconfig_values (a table storing possible software configuration values):
id - value
----------
1 - /opt/software
2 - /usr/local
3 - /usr
4 - 3.0
5 - 4.0
6 - 5.0
7 - 2007-03-04
8 - 2007-02-02

systems (a table storing systems in the environment):
id - system
-----------
1 - one.company.org
2 - two.company.org
3 - three.company.org

swconfig_name_val_xref (a cross-reference table showing instances of all 3. This is the table that is gonna get HUGE):
swconfig_name.id - swconfig_value.id - system.id
------------------------------------------------
1 - 1 - 3
2 - 5 - 3
3 - 7 - 3

Now, when I gather this information from the real world, I get just the values so I have to look up the name of the attribute in the swconfig_names table for an ID, I have to look up the value in the swconfig_values table for an ID, and finally I have to look up the system ID in the systems table. I've created a Perl application to do this load for me, but as you can imagine, it's rather slow. Here's what it does to insert a single row into the xref table:

1. Lookup the ID for the current attribute name in swconfig_names (if it doesn't exist, insert the current attribute and retrieve an ID for it)
2. Lookup the ID for the current value name in swconfig_values (if it doesn't exist, insert the current value and retrieve an ID for it)
3. Lookup the ID for the current system name in systems (if it doesn't exist, insert the current system name and retrieve an ID for it)
4. Insert the final result of all 3 lookups as a single entry in swconfig_name_val_xref.

This HAS to be a fairly common practice if anyone out there follows normalization, but is there an easier way to do it?



Edited 1 time(s). Last edit at 07/11/2007 05:13PM by Brad Edwards.