Vertical Growing/Dynamic Growth In Access Tables

[VanlanJL]

Okay this is very complicated (for me), but i need some help on this please.

I am creating a Network Diagram in Visio, along with this I have collected
all the Information for every Network Device in my companys organization for
each location across the country. I have placed this all in a a Excel
spreadsheet, and exported to Access. I will then Export from Access to SQL
Server. (I am doing this becuase I understand access better than SQL.) From
the SQL Server I will link the data to Visio. The end result, when the user
is viewing the Viso HTML they can see the data for each device,(example
Device name, IP Address, etc)

Yesterday I approached a DB tech in my company to take a look at my Access
database to make sure it was correct and etc. He sggested that I utilize a
"Vertical Growing/Dynamic Growth Table." I have never heard of this and he
tried to explain it to me but it was way over my head.

Does anybody know about this, and/or know of any sites that I might be able
to learn more about it?Below I have listed my table structure and then the
table he created:
tblAddress
LocationID (PK)
Address1
Address2
Address3
City
State
ZipCode

tblContacts
ContactID (PK)
LocationID (FK)
POC1FirstName
{etc}

tblManufacturer
MFGID (PK)
Manufacturer

tblDevice
LocationID (FK)
DeviceName (PK)Manufacturer
Mode
lIPAddress
SerialNUmber
SoftwareVersion
NumberOfPorts
SubnetMask
EndOfSubnet
Gateway
Description
Type
Addressing
Capacity
UniqueID
SWServer
VendorID
CPELOC
Channels
PartNumber
MFGID (FK)

In the above table, every location has certain types of devices, but not
every device has data for every one of the fileds listed above. Example: a
Cisco router contains data in only 5 of the fileds, but a IP DSL MODEM has
data in 7 of the fileds. Every device has data in the LocationID, DeviceName,
Manufacturer.

Below is the table my database friend created to show me the vertical growth
technique that I do not understand:
tblDeviceAttributes
DeviceName (PK
)AttributeName(PK)
AttributeValue

It looks something like this in the datasheet view:
Device Name AttributeName AttributeValue
AZ01Client01 EndOfSubnet ###.###.###.###
AZ01Client01 Type IEE 802.3
CA24Client01 Description Client Lan
CA24Client01 EndOfSubnet ###.###.###.###
CA24Client01 SubnetMask 255.128

Hope this helps, any help would be greatky apprecitaed!

 

[Roger Carlson]

What your collegue is describing is also called a "Normalized" design. It
means that instead of having a separate COLUMN for each type of device, you
have a separate ROW.

I have a 5-part blog series which gives an introduction to the concept of
Normalization. You can find the first part here:
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.html

Perhaps that will help.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L