More Table Structure Help PLEASE

[Wally Steadman]

Greetings all,

I am killing myself on structure here

I started building a DB to track Computers/Devices.

There are multiple Sites and at each site there are multiple buildings and
multiple Vlans, but a machine can only belong to one Vlan, in One building
at one site. Where I am getting stuck is basic structure of table


On a site there are multiple Vlans and most are attached to a specific
building, but some can be attached to multiple buildings.

Example:

Site A
Vlan1 Bldg1 Machine A
Vlan 2 Bldg1 Machine B
Vlan20 Bldg 1 Machine C

Vlan6 Bldg2 MachineD
Vlan9 Bldg2 MachineE
Vlan20 Bldg2 MachineF
Vlan22 Bldg2 MachineG

Vlan12 Bldg3 MachineH
Vlan13 Bldg3 MachineI
Vlan22 Bldg3 MachineJ

Vlan 1 and 2 are specific to building 1
Then notice how Vlan 20 resides in Bldg1 and Bldg2

Vlan 6 and 9 are specific to bldg2
But Vlan 20 resides in bldg 1 and 2

Vlan 12 and 13 are specific to Bldg 3
But Vlan 22 resides in Bldg 2 and 3

My table Structure is as follows:

tblSite:
SiteID - Autonumber (PK)
SiteName - Text

tblVlan:
VlanID - Autonumber (PK)
VlanName - Text
SiteID - Number (FK)(Many in a 1-M with tblSite)
Subnet - Text
Gateway - Text
Group - Text

tblMachine:
MachineID - Autonumber (PK)
VlanID - Number (FK)(Many in a 1-M with tblVlan)
MachineName - Text
MachineType - Text (Will have a lookup table for different types of
machines)
MachineIP - Text (will only be the last octect of the IP address as the
first three are obtained from Subnet in VLAN table)
Building - Text

Where I am getting myself stuck is on those VLANs that reside in multiple
buildings. I could add the building field to the Vlan table but when I
wrote the Vlans that resided in multiple buildings I would have a blank
field so then if I did a search, for the Machine, I would not know what
building it was located in. That is why I went with putting the building as
a field in the Machine Table and then have the lookup only show the
buildings located at the current site. Not sure if there is a more
efficient way to do the structure or not. I thought this was going to be a
piece of cake and it probably is, but I am letting myself get confused, so
rather than stay confused I am bringing it here to ask advice on structure.
Any help would be appreciated.

TIA
Wally Steadman
ICQ: 11904383 (Please reference "Access Newgroup if ICQ'ing)

 

[tina]

There are multiple Sites and at each site there are multiple buildings and

multiple Vlans, but a machine can only belong to one Vlan, in One building
at one site.

suggest you focus on the straightforward relationships first. a machine or
device is housed in a specific building, which sits on a specific site. each
machine or device is connected to a Vlan (i'm assuming that's some kind of
network?). tracking where a Vlan is, is secondary to the stated purpose of
the database, which is to track machines and devices (not Vlans).

tblSites
SiteID - Autonumber (PK)
SiteName - Text

tblBuildings
BldgID - Autonumber (PK)
BldgName - Text
SiteIDfk - foreign key from tblSites

tblVlans
VlanID - Autonumber (PK)
VlanName - Text
Subnet - Text
Gateway - Text
Group - Text

tblVlanBldgs
VlanID - foreign key from tblVlans
BldgID - foreign key from tblBuildings
(you can use the above fields as a composite primary key, or add an
Autonumber field as a surrogate primary key.)

the one thing that's not clear to me: you say that one Vlan may reside in
multiple buildings, but what is the other side of the relationship? may one
building have multiple Vlans? or only one Vlan? the above linking table will
support a many-to-many relationship: one Vlan may reside in multiple
buildings, *and* one building may have multiple Vlans. if instead it's a
one-to-many relationship: one Vlan may reside in multiple buildings, *but*
each building has only one Vlan, then don't use the above linking table;
just add the VlanID field to tblBuildings as a foreign key field from
tblVlans.

tblMachines
MachineID - Autonumber (PK)
VlanID - Number (FK)(Many in a 1-M with tblVlan)
BldgID - foreign key from tblBuildings
MachineName - Text
MachineType - Text (Will have a lookup table for different types of
machines)
MachineIP - Text (will only be the last octect of the IP address as the
first three are obtained from Subnet in VLAN table)

again, if each building may have only one Vlan, then remove the VlanID field
from tblMachines, since BldgID will provide an indirect link to the Vlan
table.

hth

 

[Wally Steadman]

Tina,
thanks for your response. I am not really understanding what you are
saying about the many to many tables (the tblVlanBldg) and what it would be
used for. It is important to track machines by VLAN and BUILDING. The
VLANS and BUILDINGS are specific to a SITE. A Vlan is in fact a network so
it is the Logical Location (actually more important than building) and
Building is the Physical Location of a machine. There can be, and most
likely will be more than one VLAN in a BUILDING and a VLAN can reside in
more than one building.

Example would be- one building at Site A is the ARTS BUILDING and it has
three VLANS one called Fine Arts, one called Theater and another called
management. There are machines connected to each VLAN. The Fine Arts and
Theater VLANS will reside ONLY in the ARTS building. But the management
VLAN will reside in ALL buildings at site A.

It is important to know the Logical and Physical Locations because if a Tech
has to find a machine he/she must know the building, and if they have to
make configuration changes to the machine, they have to know the VLAN
information.

I hope that made the scenario a little clearer for you and could you expound
on the many to many aspect based on this scenario?

Thanks again

Wally Steadman

 

[tina]

okay, just as i said, there's a many-to-many relationship between Vlans and
buildings: one Vlan may reside in many buildings, *and* one building may
host many Vlans.

to define the relationship between any two entities, you must declare BOTH
"sides" of the relationship, as above. read again in my last post the
declaration of a many-to-many relationship between Vlans and buildings
(which is the same as stated above), and compare it to the declaration of a
one-to-many relationship between the entities. i think you'll see the
difference between the two declarations.

just to be clear, then, here is how i suggest you set up your tables, as

tblSites
SiteID - Autonumber (PK)
SiteName - Text

tblBuildings
BldgID - Autonumber (PK)
BldgName - Text
SiteIDfk - foreign key from tblSites

tblMachines
MachineID - Autonumber (PK)
BldgIDfk - foreign key from tblBuildings
VlanIDfk - foreign key from tblVlans
MachineName - Text
MachineType - Text (Will have a lookup table for different types of
machines)
MachineIP - Text (will only be the last octect of the IP address as the
first three are obtained from Subnet in VLAN table)

tblVlans
VlanID - Autonumber (PK)
VlanName - Text
Subnet - Text
Gateway - Text
Group - Text

tblVlanBldgs
VlanIDfk - foreign key from tblVlans
BldgIDfk - foreign key from tblBuildings
(this is the linking table that supports the many-to-many relationship
between tblVlans and tblBuildings. you can use the above fields as a
composite primary key, or add an Autonumber field as a surrogate primary
key.)

the relationships are:

tblSites.SiteID 1:n tblBuildings.SiteIDfk
tblBuildings.BldgID 1:n tblMachines.BldgIDfk
tblBuildings.BldgID 1:n tblVlanBldgs.BldgIDfk
tblVlans.VlanID 1:n tblMachines.VlanIDfk
tblVlans.VlanID 1:n tblVlanBldgs.BldgIDfk

each site is directly connected to all the buildings on it. each building is
directly connected to all the machines it houses, and also to all the Vlans
it hosts. each Vlan is directly connected to all the machines it supports,
and also to all the buildings it resides in. you can easily find out what
site a machine is at, or what site(s) a Vlan supports, because each of those
entities is connected to buildings, and buildings are directly connected to
sites.

hth

 

[tina]

well, okay, let me tweak the design a little (i just got up, and my brain is
slow to turn on). since a machine can only be connected to one Vlan, i'd
probably set up the tables/relationships like this, as

tblSites
SiteID - Autonumber (PK)
SiteName - Text

tblBuildings
BldgID - Autonumber (PK)
BldgName - Text
SiteIDfk - foreign key from tblSites

tblVlans
VlanID - Autonumber (PK)
VlanName - Text
Subnet - Text
Gateway - Text
Group - Text

tblVlanBldgs
VBID - Autonumber (PK)
VlanIDfk - foreign key from tblVlans
BldgIDfk - foreign key from tblBuildings
(this is the linking table that supports the many-to-many relationship
between tblVlans and tblBuildings.)

tblMachines
MachineID - Autonumber (PK)
VBIDfk - foreign key from tblVlanBldgs
MachineName - Text
MachineType - Text (Will have a lookup table for different types of
machines)
MachineIP - Text (will only be the last octect of the IP address as the
first three are obtained from Subnet in VLAN table)

the relationships are:

tblSites.SiteID 1:n tblBuildings.SiteIDfk
tblBuildings.BldgID 1:n tblVlanBldgs.BldgIDfk
tblVlans.VlanID 1:n tblVlanBldgs.VlanIDfk
tblVlanBldgs.VBID 1:n tblMachines.VBIDfk

okay, that's a little neater and cleaner than the setup i posted a few
minutes ago. since the linking table tblVlanBldgs explicitly defines each
Vlan in each building, it makes sense to simply link each machine to a
specific Vlan/Bldg record, rather than linking each machine to each building
and each Vlan with separate fields in tblMachines.

hth

 

[Wally Steadman]

Tina
Again thanks for helping me understand the Many to many. I noticed in the
tblVlan, there is no relationship to the Site. There are many sites and
each site will have seperate adn distinct Vlans (but could be named the
same) as well as Buildings, so should I associate the Vlan to the Site? I
have posted a copy of my current relationships here
http://www.steadmanusa.com/tablerelate.htm
Does that create the Many to Many relationship you were referring to?

 

[Guest]

I would suggest a small change to tina's tables, because even though a
building is always associated with a specific site, it is actually the
combination of site, building, and vlan that is important for each computer.

 

[tina]

tblVlanBldgSites
i'm afraid i must disagree with you there, hon. each building is only at one
site, and there is already a direct link between buildings and sites, in
tblBuildings. the SiteID field does not belong in the linking table between
buildings and Vlans; it has nothing directly to do with the relationship
between those two entities.

hth

 

[tina]

comments inline.

Tina
Again thanks for helping me understand the Many to many. I noticed in the
tblVlan, there is no relationship to the Site. There are many sites and
each site will have seperate adn distinct Vlans (but could be named the
same) as well as Buildings, so should I associate the Vlan to the Site?

no. each Vlan is already associated with a specific site, through the
linking table which pairs each Vlan with one or more specific buildings -
and each building is directly linked to a specific site in tblBuildings.
linking a Vlan directly to a site is redundant and unnecessary. this is a
relational data model, so use it relationally; every entity does not need to
have a *direct* link to every related entity.

as far as buildings at different sites having identical names, or Vlans at
different sites (or in different buildings) having identical names, you'll
have to consider the totality of data about each Vlan to differentiate
between those that happen to have identical names. that includes the site
that hosts a specific Vlan, of course; but that does NOT mean that you
*must* have a direct link between tblVlans and tblSites.

I
have posted a copy of my current relationships here
http://www.steadmanusa.com/tablerelate.htm
Does that create the Many to Many relationship you were referring to?

the relationship diagram you posted does not have a many-to-many
relationship anywhere in it. it does not define and support the relationship
between Vlans and Buildings that you described earlier in the thread.

my last recommendation still stands. suggest you try building the tables and
relationships as i outlined. perhaps seeing them in a relationship diagram
will help you understand how they work.

here are my suggested tables/relationship, one more time for clarity:

tblSites
SiteID - Autonumber (PK)
SiteName - Text

tblBuildings
BldgID - Autonumber (PK)
BldgName - Text
SiteIDfk - foreign key from tblSites

tblVlans
VlanID - Autonumber (PK)
VlanName - Text
Subnet - Text
Gateway - Text
Group - Text

tblVlanBldgs
VBID - Autonumber (PK)
VlanIDfk - foreign key from tblVlans
BldgIDfk - foreign key from tblBuildings
(this is the linking table that supports the many-to-many relationship
between tblVlans and tblBuildings.)

tblMachines
MachineID - Autonumber (PK)
VBIDfk - foreign key from tblVlanBldgs
MachineName - Text
MachineType - Text (Will have a lookup table for different types of
machines)
MachineIP - Text (will only be the last octect of the IP address as the
first three are obtained from Subnet in VLAN table)

the relationships are:

tblSites.SiteID 1:n tblBuildings.SiteIDfk
tblBuildings.BldgID 1:n tblVlanBldgs.BldgIDfk
tblVlans.VlanID 1:n tblVlanBldgs.VlanIDfk
tblVlanBldgs.VBID 1:n tblMachines.VBIDfk

hth

 

[Wally Steadman]

Thanks Tina. I created the tables and relationships and it became clear to
me what you were talking about. Appreciate all your help

Wally Steadman

 

[tina]

you're very welcome

Thanks Tina. I created the tables and relationships and it became clear to
me what you were talking about. Appreciate all your help

Wally Steadman

 

[Wally Steadman]

Tina,
It has taken me a while to get back here but I have built the database
based on the structure discussed and I only run in to one issue that maybe I
am not looking at properly. Due to the relationships set up, when I delete
a site, referential integrity deletes the buildings associated with it just
fine and right on down the line. But since there is no direct assoction
with the VLAN's, when I delete a site, the VLANS remain in tblVLAN. While
this probably would not bother anything for quite some time, I keep asking
myself if that is sound. Was wondering if creating a 1-M between tblSites
and tblVlan is bad form and if so, is there a way to get it to delete the
VLAN's that are no longer in use because a site has been deleted?
Here is my layout with tables and relationships.
www.steadmanusa.com/design.htm
I also have a copy of a the database on the webpage with sample data.
Would appreciate any advice on this. I am slowly but surely learning the
1-M relationships, but sure I don't have it completely yet.

TIA

Wally Steadman

 

[tina]

well, you could change the table structure of VLans, adding a foreign key
field from tblSites. the redundancy bothers me, though, because you have the
potential for data entry error, resulting in conflicting data such as: VLan
"x" is in Building "y" (tblVLanBldgs), but VLan "x" is linked to Site "A"
(tblVlans), while Building "y" is linked to Site "B" (tblBuildings).

i might simply run an Delete query after deleting a Site record, to delete
the record from tblVlans that has no child record in tblVlanBuildings.

hth