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

[Guest]

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]

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

 

[Guest]

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,

 

[Guest]

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]

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

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.

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?

 

[John Nurick]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

thanks immensely

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.

 

[Guest]

SEe my in line note.

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


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


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.





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.

 

[Guest]

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.

I believe you are correct.

 

[Guest]

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


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


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.





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

The nodes do get moved from one transmitter to another. They can see the
moves by the change in name. I'm not sure exactly how to track the move but
I assumed the date created and date destroyed date was the way to do that.
Also in a query I could use the node name and the date destroyed as a
combination match key to see the history?

 

[Guest]

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


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


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.)

This may be the answer to my earlier question. Sorry for the multiple posts
but I understand it more and more. I see there is a one to many from the
tblNodes to tblNode Names. The ParentNodeName is where I create the
relationship to the tblNodeNames (childNodeName)?

Do you mean by the tracking of the movement of the nodes that I have to
create a user interface for the user to do the data input? If so I did plan
on doing that some way.

 

[Guest]

Is there a reason for not putting the transmitter in the port table since it
is a 1:1? Is the reason to have a separate table because you have to move
the transmitter with the nodes? I'm guessing why you did that but I noticed
in Access it doesn't have a 1:1 so I guess it is the same as 1:M on the
default join.

 

[John Nurick]

I don't know enough about these entities to say very much.

If each port only

Is there a reason for not putting the transmitter in the port table since it
is a 1:1? Is the reason to have a separate table because you have to move
the transmitter with the nodes? I'm guessing why you did that but I noticed
in Access it doesn't have a 1:1 so I guess it is the same as 1:M on the
default join.

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.

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?

 

[John Nurick]

[previous message sent in error]

I don't know enough about these entities to make confident
pronouncements. But:

1) If every Port has exactly one Transmitter and you don't need to
track Transmitters separately from Ports, then you can treat a
Transmitter as an attribute of a Port. For instance, all you might
need are a fields like these in the Ports table:
TransmitterModel
TransmitterSerialNumber
DateTransmitterInstalled

2) If you need to track Transmitters separately from Ports (e.g. each
functioning Port has exactly one Transmitter at any given time, but
any given Transmitter may be connected to various Ports during its
life, or may go for repair, or whatever) - then you have a M:M
relationship between Ports and Transmitters.

3) In Access, a 1:1 relationship is the same as a 1:M relationship -
except that each of the linked fields has a unique index (in a 1:M
relationship the '1' side has a unique index while the index on the
'M' side allows duplicates. In a 1:1 relationship the linked fields
are typically (but not necessarily) the primary keys.

Hope this helps!

Is there a reason for not putting the transmitter in the port table since it
is a 1:1? Is the reason to have a separate table because you have to move
the transmitter with the nodes? I'm guessing why you did that but I noticed
in Access it doesn't have a 1:1 so I guess it is the same as 1:M on the
default join.

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.

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?