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
Model
IPAddress
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.Be low is the table my database friend created to show me the vertical growth technique that I do not understand:
tblDeviceAttrib utes
DeviceName (PK)
AttributeName(P K)
AttributeValueI t

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!

[Delerna]

I will try and explain in simple terms why vertical growth is better than horizontal growth, so experts please don't jump on me.
I suggest that you need to research and study this more because I am explaining this in very simplistic terms and not being complete in any sense.

Each time a new device is invented for a computer you will need to add more fields to your device table. This is called horizontal growth.
The problem with horizontal growth is that now you will have to change each and every query, form, report, vba code that uses that table to accomodate the new fields.

Wouldn't it be better to be able to just add in the new device and all the queries, forms, reports and vba code automatically recognise the new device.

Thats what vertical growth provides.
Vertical growth incorporates displaying information in list form

So instead of having a field to hold the value for each device in the device table (horizontal growth) you have a new table that holds an ID and a device Name and each time you acquire a new type of device it gets added to that table(vertical growth).

Now your device table only needs 3 fields
LocationID (from your tblAdresses)
DeviceID (from the new table)
Device Value (the value you would have entered when using horizontal growth)

Now, each time a device is acuired at a location you simply add a new record for that location, enter the device id (Type of Device) and the value for that device....(Vert ical Growth)

No more changes are necessary because you have designed your queries, forms, reports and vba code with vertical growth in mind. The new device will simply appear in the list for that particular address.

Another benefit,

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.

That will no longer apply. Every address will only have records for devices that are actually there....This is called "Normalization" , another thing you might want to research.

Anyway, I hope that helps you understand it a bit better.

[mshmyob]

As usual, Delerna has explained it perfectly.

Vertical growth just means that your design adds rows (records) to a table whereas horizontal growth means you add new columns (fields) to the table.

Horizontal design means that you need to keep changing the table structure (not wise to do).

Lets look at a simple example (this also is for slowly changing members in dataware housing).

Lets assume you have a simple table that needs to track a product price over time.

A vertical design may have 3 fields (ProductID, Date, and Price - where ProductID and Date are a composite primary key). You can just keep adding rows to keep track of the historical change in price for the product over time. Here we have multiple records for the same product but a historical price chaneg for that product.

A horizontal table design would have maybe something like a ProductID with a Date1 and Price1 field. The next time the price changes for the product you would add another 2 columns (fields) may called Date 2 and Price2 and so on forever. Here we have one record for a specific product but we constantly need to keep adding a new fields to the table.

Either way we have the exact same historical price change data but the vertical table design needs only be created once and left alone.

cheers,

[Delerna]

Horizontal growth is something that is easy to understand when you begin developing databases.

Vertical growth is one of those simple ideas that has big benefits that sounds daunting when first met.
Trust me, after you've done it a couple of times you will wonder why you ever thought it was difficult.

And you will never go back to horizontal growth.

[vanlanjl]

where could i find more information on this. I keep getting links to "Normalizat ion"

My databse is "Normalized " but not in this manner.

[mshmyob]

where could i find more information on this. I keep getting links to "Normalizat ion"

My databse is "Normalized " but not in this manner.

Actually looking at it I would say it is not normalized.

Take for instance your table tblDevice. You have a manufacture field thus duplicating the Manufacturer name even though you have a foreign key back to the Manufacture table. Also the design of the structure only allows a single device for each location. ie: Location 1 can only have a single Cisco Router (1 to M) where you probably want a many to many relationship with a bridge table between location and device so that each location can have many of the same type of device.

So I would have to differ with your opinion that it is normalized. Of course I don't know your business rules so I can't give further guidance.

cheers,

[vanlanjl]

Im not sure I follow. Each location has mulitple devices, multiple devices of the same type, each device has its own device name which is the PK. When i log into a router it has a host name and the is the same as the DeviceName in the database. So I do have for example multiple switches in one location they are just each defined by their host name (DeviceName)

[mshmyob]

Im not sure I follow. Each location has mulitple devices, multiple devices of the same type, each device has its own device name which is the PK. When i log into a router it has a host name and the is the same as the DeviceName in the database. So I do have for example multiple switches in one location they are just each defined by their host name (DeviceName)

This paragraph confirms that it is not normalized.

You are saying that Device is related to Location with the following business ruels:

Each location can have many devices
Each device can be in many locations.

Therefore you need a table for location (which you have) and a table for devices (which you have but is setup incorrectly) and a bridge table to accomodate the M:M relationship.

The table devices should contain common information about a device such as the following in its simplistic form

DeviceID - autonumber
Device Description - ie: Router, Switch, PS, Server, etc.
ManufactererID - FK to Manufactuer Table
etc...

The Bridge table should contain something like so (it will contain the details about the device):

DeviceID - FK
LocationID - FK
DeviceSerial - PK
DeviceIP
DeviceSubnet
etc,,,,

Look at your current device table and look at all the redundancy - Model #, Ports, Manufactuerer, Capacity, Type, etc etc.

I would go as far as saying you might also need a model table at a minimum.

cheers,

[Delerna]

Again I am speaking simply here


one way of thinking about the first normal form (1NF) is

No redundant fields.
Meaning if a field is sometimes used and sometimes not depending on the record then that field doesn't belong in this table.

From your first post

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.

So your table is not in 1NF.
Before your database can be in 2nd normal form (2NF) all tables must be in 1NF
Before your database can be in 3NF all tables must be in 2NF

IE. it is not normalized...We are not trying to critcise you here, but to assist you.

where could i find more information on this. I keep getting links to "Normalizat ion"

The reason for this is because Normalization tends to automatically lead you towards vertical growth. They are like two sides of the same coin.

Remember I am being simplistic with my comments here.

[MMcCarthy]

Check out this article in our Insights section on Normalisation. It may help you to understand what everyone is talking about.

Database Normalization and Table Structures


Mary