simple structure question with complicated data[x-post from design

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

I thought it was all one to many and it seemed simple.you are right. routers
have ports. The transmitter however is a separate entity one object without
ports. The individual nodes are separate entities and get moved around. So
it is re- wired to the transmitters and routers. The node doesn't get
destroyed it just gets moved. If you are counting a move as a new node then
possibly it was destroyed in that sense.

HOWEVER, they would like to see the old node without any utilization on the
report to show what happened. If the old ports are shown could this be a
many to many?The physical reality is the node is gone but on the report they
want to see the old data. QUESTION:So how do I show this in a db?

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

When a node gets segmented part of its traffic gets routed to a new
transmitter. The node doesn't change. Then it gets complicated. The old
node ABCD is dead. It has no utilization and is in effect destroyed, but
they want to see what happened to it. 2 new nodes are created from the one
ABCD-A and ABCD-B.

So now on this murky report I have to show all 3. QUESTION: How do I do that?

thanks,

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

Question number 2: Would it work without having a many to many from ports to
transmitters if I show the destroyed ports and the alive ports in the report?
Its a many to many but I suppose I could add the date field and then it
would be unique.

thanks a lot John.

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

Janis,

Next time please post your reply as a response in the existing thread.
Creating a new thread like this makes for confusion.

I still don't understand the situation. You say

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

Presumably this should have been

Area 1:M-- Router 1:M-- Ports--1:1 Transmitter-- 1:M Nodes

If that's the case, the tblNodes table I proposed in my last message

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed

should be more like

tblNodes
tblNodeName* (assuming the node names such as ABCD
and ABCD-A are reliably unique and stable)
TransmitterID (foreign key into tblTransmitters)
DateCreated
ParentNodeName (the node from which this node was split off
or segmented.)

A normalised structure based around this will let you produce the
reports you want (as I understand them).

More commments inline.



On Mon, 30 Jul 2007 09:04:00 -0700, Janis
wrote:

I thought it was all one to many and it seemed simple.you are right. routers
have ports. The transmitter however is a separate entity one object without
ports. The individual nodes are separate entities and get moved around. So
it is re- wired to the transmitters and routers. The node doesn't get
destroyed it just gets moved.

Do Nodes get moved from one Transmitter to another? If they do, do you
need to track their movements?

If you are counting a move as a new node then
possibly it was destroyed in that sense.

HOWEVER, they would like to see the old node without any utilization on the
report to show what happened. If the old ports are shown could this be a
many to many?The physical reality is the node is gone but on the report they
want to see the old data. QUESTION:So how do I show this in a db?

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

When a node gets segmented part of its traffic gets routed to a new
transmitter. The node doesn't change. Then it gets complicated. The old
node ABCD is dead. It has no utilization and is in effect destroyed, but
they want to see what happened to it. 2 new nodes are created from the one
ABCD-A and ABCD-B.

So now on this murky report I have to show all 3. QUESTION: How do I do that?

thanks,

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP

In your previous message you said there was a 1:1 relationship between
Transmitters and Ports. And this is the first mention of Ports being
destroyed (and if they're destroyed they also need to be created).

I think you need to take a step back, maybe with the aid of a book on
relational database design, and think about the various entities you
need to model and how they are related in real life.

On Mon, 30 Jul 2007 09:34:01 -0700, Janis
wrote:

Question number 2: Would it work without having a many to many from ports to
transmitters if I show the destroyed ports and the alive ports in the report?
Its a many to many but I suppose I could add the date field and then it
would be unique.

thanks a lot John.

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP

All I can say is thanks you helped me a great deal. I guess there is a
router 1 to many to the ports otherwise you wouldn't know which port it was
attached to. I didn't know I was starting a new thread so I will reply to
the thread and try to keep all my replies in one reply.

"John Nurick" wrote:

Janis,

Next time please post your reply as a response in the existing thread.
Creating a new thread like this makes for confusion.

I still don't understand the situation. You say

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

Presumably this should have been

Area 1:M-- Router 1:M-- Ports--1:1 Transmitter-- 1:M Nodes

If that's the case, the tblNodes table I proposed in my last message

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed

should be more like

tblNodes
tblNodeName* (assuming the node names such as ABCD
and ABCD-A are reliably unique and stable)
TransmitterID (foreign key into tblTransmitters)
DateCreated
ParentNodeName (the node from which this node was split off
or segmented.)

A normalised structure based around this will let you produce the
reports you want (as I understand them).

More commments inline.



On Mon, 30 Jul 2007 09:04:00 -0700, Janis
wrote:

I thought it was all one to many and it seemed simple.you are right. routers
have ports. The transmitter however is a separate entity one object without
ports. The individual nodes are separate entities and get moved around. So
it is re- wired to the transmitters and routers. The node doesn't get
destroyed it just gets moved.

Do Nodes get moved from one Transmitter to another? If they do, do you
need to track their movements?

If you are counting a move as a new node then
possibly it was destroyed in that sense.

HOWEVER, they would like to see the old node without any utilization on the
report to show what happened. If the old ports are shown could this be a
many to many?The physical reality is the node is gone but on the report they
want to see the old data. QUESTION:So how do I show this in a db?

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

When a node gets segmented part of its traffic gets routed to a new
transmitter. The node doesn't change. Then it gets complicated. The old
node ABCD is dead. It has no utilization and is in effect destroyed, but
they want to see what happened to it. 2 new nodes are created from the one
ABCD-A and ABCD-B.

So now on this murky report I have to show all 3. QUESTION: How do I do that?

thanks,

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP

The ports are not really created at least in this db. That all gets done in
a SQL db. For this db it only gets divided but it does get changed. The
port has the same name but it gets an A letter. So the port ABCD would
become ABCD-A on the old transmitter and ABCD-B on a new transmitter. So
the old port ABCD is really does not have any utilization but it still needs
to show up on the report.
I'M SORRY I WAS SAYING DESTRUCTION OF PORTS I MEANT DESTRUCTION OF NODES
AS YOU NOTED EARLIER.
Thanks again for your help it was extremely beneficial.
"John Nurick" wrote:

In your previous message you said there was a 1:1 relationship between
Transmitters and Ports. And this is the first mention of Ports being
destroyed (and if they're destroyed they also need to be created).

I think you need to take a step back, maybe with the aid of a book on
relational database design, and think about the various entities you
need to model and how they are related in real life.

On Mon, 30 Jul 2007 09:34:01 -0700, Janis
wrote:

Question number 2: Would it work without having a many to many from ports to
transmitters if I show the destroyed ports and the alive ports in the report?
Its a many to many but I suppose I could add the date field and then it
would be unique.

thanks a lot John.

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP

thanks immensely

"John Nurick" wrote:

In your previous message you said there was a 1:1 relationship between
Transmitters and Ports. And this is the first mention of Ports being
destroyed (and if they're destroyed they also need to be created).

I think you need to take a step back, maybe with the aid of a book on
relational database design, and think about the various entities you
need to model and how they are related in real life.

On Mon, 30 Jul 2007 09:34:01 -0700, Janis
wrote:

Question number 2: Would it work without having a many to many from ports to
transmitters if I show the destroyed ports and the alive ports in the report?
Its a many to many but I suppose I could add the date field and then it
would be unique.

thanks a lot John.

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP

SEe my in line note.

"John Nurick" wrote:

Janis,

Next time please post your reply as a response in the existing thread.
Creating a new thread like this makes for confusion.

I still don't understand the situation. You say

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

Presumably this should have been

Area 1:M-- Router 1:M-- Ports--1:1 Transmitter-- 1:M Nodes

If that's the case, the tblNodes table I proposed in my last message

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed

should be more like

tblNodes
tblNodeName* (assuming the node names such as ABCD
and ABCD-A are reliably unique and stable)
TransmitterID (foreign key into tblTransmitters)
DateCreated
ParentNodeName (the node from which this node was split off
or segmented.)

A normalised structure based around this will let you produce the
reports you want (as I understand them).

More commments inline.



On Mon, 30 Jul 2007 09:04:00 -0700, Janis
wrote:

I thought it was all one to many and it seemed simple.you are right. routers
have ports. The transmitter however is a separate entity one object without
ports. The individual nodes are separate entities and get moved around. So
it is re- wired to the transmitters and routers. The node doesn't get
destroyed it just gets moved.

Do Nodes get moved from one Transmitter to another? If they do, do you
need to track their movements?

YES nodes do get moved to another transmitter when they get segmented
because that is the purpose to allow more traffice on another transmitter.

If you are counting a move as a new node then
possibly it was destroyed in that sense.

HOWEVER, they would like to see the old node without any utilization on the
report to show what happened. If the old ports are shown could this be a
many to many?The physical reality is the node is gone but on the report they
want to see the old data. QUESTION:So how do I show this in a db?

Area 1:M-- Router Ports---1:1Transmitter----1:M Nodes

When a node gets segmented part of its traffic gets routed to a new
transmitter. The node doesn't change. Then it gets complicated. The old
node ABCD is dead. It has no utilization and is in effect destroyed, but
they want to see what happened to it. 2 new nodes are created from the one
ABCD-A and ABCD-B.

So now on this murky report I have to show all 3. QUESTION: How do I do that?

thanks,

"John Nurick" wrote:

Janis,

This structure doesn't look simple to me. Certainly I can't understand
it. This

area--router--transmistter--nodeID--statistics

implies to me that there are 1:M relationships between area and
router, router and transmitter, transmitter and node, and node and
item of statistical data. But elsewhere you say

there is a one to one relationship from the transmitter to the nodes.

and also mention transmitter ports, which seem to be related M:1 to
transmitters.

As far as I can make out the object of the exercise is to collate and
report on usage statistics (by node? port? transmitter?). This table
confuses me:

statisticsID
weekending
internet traffic
t.v. traffic

It seems to me that this should include a NodeId field.

....

At this stage I feel that the heart of your structure needs to be one
or more tables that track the creation (and, presumably, at least in
principle, destruction) of nodes. Maybe something along these lines:

tblNodes
NodeID*
PortID (assuming a 1:M relationship between Ports and Nodes)
DateCreated
ParentNodeID (the node from which this node was split off)
DateDestroyed






On Sun, 29 Jul 2007 18:50:01 -0700, Janis
wrote:

I have a really simple structure regarding a db for an ISP. I need get weekly
utilization figures which I get from Excel but would like to do in Access.
I'm cross posting this from the design section since
no one answered it.

area--router--transmistter--nodeID--statistics

areaID
area

transmitterID
AREAID
transmitter ports

nodeID
transmitterId
nodes

statisticsID
weekending
internet traffic
t.v. traffic

A tricky part is they split the node if the utilization is over 50% and
move it to a new router. The node is the pedestal that is connected to the
subscribers.

Each node has groupings of 4 letter names, for example
ABCD,EFGH. The related transmitter ports sometimes have the exact same name
ABCD,EFGH. To split the transmitter ,they take one port, for example, ABCD
and call it A, ABCD-A and
ABCD-B to show the split on the transmitter. They take the first node ABCD
corresponds to transmitter ABCD-A and the 2nd EFGH and it corresponds to
transmitter ABCD-B to split it if the utilization is over 50%. Sometimes
there are more nodes than transmitter ports the ports and nodes aren't
always matching in number or names.

there is a one to one relationship from the transmitter to the nodes. The
nodes are all one group and the ports are all one group hence one to one.
When it gets over 50% it gets split. Some of the nodes get attached to a new
transmiter as mentioned above. It is still unique one to one relationship
for each area -- router--transmitter to nodes. Its just the transmitter has
an A or B extension name showing it was split.

I could do a one to many from each port to each node but that wouldn't be
worth all the data input and it doesn't seem to be needed since there still
is maintained a one to one relationship from the ports to the nodes even if
the groupings are moved.



QUESTION:

Is this better to do in Excel? I get the Excel report from a nAccess db
that gets the data from SQL. If I can get to the SQL db then I could do the
queries and get the data. The problem with Excel is a new column is added
weekly for the nodes over 50% for both tv and internet. Then they have to
find which nodes on the tv side are also on the internet side so they can be
advised and try to do the work together. The user has to do all these
vlookups to find out which nodes are still over 50%. It would be easier in a
database at least . They also have to clean the data each week. It seems to
be a real mess but I want to be sure my relationships are right before I try
it.

any advice would be appreciated.
--
John Nurick - Access MVP

--
John Nurick - Access MVP