Database Design Problem

I have had very little experience designing databases and would really
appreciate some advice. I have been asked to design an Access database to
track servers, the software on each server and the hardware the servers are
on. This appeared to be straight forward until they told me they are
implementing virtual servers and the virutal servers will be on Clusters.
They will still also have standalone servers. So far I have designed the
following tables but I am unsure if I am handling the Clustering correctly.
The information I have been given is as follows -
A server can be standalone on a piece of hardware.
A server can be a virtual server in a cluster. Many virutual servers can
reside in a cluster. A cluster can have many pieces of hardware.
I have designed the following tables -
TblServer -
Server Id
Server Name
Description
Hardware Id
Cluster Id
TblHardware -
Hardware Id
Serial Number
Manufacturer
Server Id
TblClusters -
Cluster Id
Cluster name
TbleServerSoftware -
Server software Id
Server Id
Software Id
Date Installed
tble Software - (reference table)
Software Id
Software name
Software Version
Vendor


I have set up a 1 to many relationship between Hardware and Server
I have set up a 1 to many relationship between Clusters and Hardware

When entering datain the Servers table a server can either have a Hardware
Id or a Cluster Id but not both.

I am hoping that someone can tell me if I have designed the database
correctly or if their is a better way of doing this.
Thankyou

You left out a few details of a full description of your application and
intended solution. My gut feel is that you have an OK solution. (or, at
least the portion of tit that you've told us about. Whether or not it's
optimal depends on the details plus what you want to get out of this.

Another possibility could be a self-referencing heirarchy such as a "family
tree" arrangement where you just have a heirarchy of "entities" and have a
field to identify what type of an entity it is (software,server, cluster
etc.). This would provide more adaptability to the fact that the structure
(not just the entities) varies in each case. There have been posts on how
to do this structure. But if you are newer at this, your original plan
(which is less abstract) is probably better.

Fred

"Fiona" wrote:

I have had very little experience designing databases and would really
appreciate some advice. I have been asked to design an Access database to
track servers, the software on each server and the hardware the servers are
on. This appeared to be straight forward until they told me they are
implementing virtual servers and the virutal servers will be on Clusters.
They will still also have standalone servers. So far I have designed the
following tables but I am unsure if I am handling the Clustering correctly.
The information I have been given is as follows -
A server can be standalone on a piece of hardware.
A server can be a virtual server in a cluster. Many virutual servers can
reside in a cluster. A cluster can have many pieces of hardware.
I have designed the following tables -
TblServer -
Server Id
Server Name
Description
Hardware Id
Cluster Id
TblHardware -
Hardware Id
Serial Number
Manufacturer
Server Id
TblClusters -
Cluster Id
Cluster name
TbleServerSoftware -
Server software Id
Server Id
Software Id
Date Installed
tble Software - (reference table)
Software Id
Software name
Software Version
Vendor


I have set up a 1 to many relationship between Hardware and Server
I have set up a 1 to many relationship between Clusters and Hardware

When entering datain the Servers table a server can either have a Hardware
Id or a Cluster Id but not both.

I am hoping that someone can tell me if I have designed the database
correctly or if their is a better way of doing this.
Thankyou

Thanks Fred

The only problem I seem to have with this design is getting the following
information out of the database -

I need a report that shows me all servers, the hardware serial number or
cluster name.

I can get a report to work that shows me all servers and their serial number
but as soon as I add the cluster table and try to get the report to display a
cluster name instead of the serial number, the report returns no data. I can
also get a report that shows me the servers and the cluster name. I just
cannot work out how to get all this information is one report.

I will keep working at it there must be away.

"Fred" wrote:

You left out a few details of a full description of your application and
intended solution. My gut feel is that you have an OK solution. (or, at
least the portion of tit that you've told us about. Whether or not it's
optimal depends on the details plus what you want to get out of this.

Another possibility could be a self-referencing heirarchy such as a "family
tree" arrangement where you just have a heirarchy of "entities" and have a
field to identify what type of an entity it is (software,server, cluster
etc.). This would provide more adaptability to the fact that the structure
(not just the entities) varies in each case. There have been posts on how
to do this structure. But if you are newer at this, your original plan
(which is less abstract) is probably better.

Fred

"Fiona" wrote:

I have had very little experience designing databases and would really
appreciate some advice. I have been asked to design an Access database to
track servers, the software on each server and the hardware the servers are
on. This appeared to be straight forward until they told me they are
implementing virtual servers and the virutal servers will be on Clusters.
They will still also have standalone servers. So far I have designed the
following tables but I am unsure if I am handling the Clustering correctly.
The information I have been given is as follows -
A server can be standalone on a piece of hardware.
A server can be a virtual server in a cluster. Many virutual servers can
reside in a cluster. A cluster can have many pieces of hardware.
I have designed the following tables -
TblServer -
Server Id
Server Name
Description
Hardware Id
Cluster Id
TblHardware -
Hardware Id
Serial Number
Manufacturer
Server Id
TblClusters -
Cluster Id
Cluster name
TbleServerSoftware -
Server software Id
Server Id
Software Id
Date Installed
tble Software - (reference table)
Software Id
Software name
Software Version
Vendor


I have set up a 1 to many relationship between Hardware and Server
I have set up a 1 to many relationship between Clusters and Hardware

When entering datain the Servers table a server can either have a Hardware
Id or a Cluster Id but not both.

I am hoping that someone can tell me if I have designed the database
correctly or if their is a better way of doing this.
Thankyou

In case you end up finding that your have a "ground up" structureal question,
you could have a dialog with the forum at that time.

One quick note / thought. Probably a core fact in your current effort is
to know and understand the specifics of the relationships that you
established. You can click on the relationship lines and fish eround in the
resultant dialog boxes that come up to see this. This is not the same thing
as the e.g. "1 to many" type attributes that you mentioned, which relates
more to the nature of the data than the nature of the join.


"Fiona" wrote:

Thanks Fred

The only problem I seem to have with this design is getting the following
information out of the database -

I need a report that shows me all servers, the hardware serial number or
cluster name.

I can get a report to work that shows me all servers and their serial number
but as soon as I add the cluster table and try to get the report to display a
cluster name instead of the serial number, the report returns no data. I can
also get a report that shows me the servers and the cluster name. I just
cannot work out how to get all this information is one report.

I will keep working at it there must be away.

"Fred" wrote:

You left out a few details of a full description of your application and
intended solution. My gut feel is that you have an OK solution. (or, at
least the portion of tit that you've told us about. Whether or not it's
optimal depends on the details plus what you want to get out of this.

Another possibility could be a self-referencing heirarchy such as a "family
tree" arrangement where you just have a heirarchy of "entities" and have a
field to identify what type of an entity it is (software,server, cluster
etc.). This would provide more adaptability to the fact that the structure
(not just the entities) varies in each case. There have been posts on how
to do this structure. But if you are newer at this, your original plan
(which is less abstract) is probably better.

Fred

"Fiona" wrote:

I have had very little experience designing databases and would really
appreciate some advice. I have been asked to design an Access database to
track servers, the software on each server and the hardware the servers are
on. This appeared to be straight forward until they told me they are
implementing virtual servers and the virutal servers will be on Clusters.
They will still also have standalone servers. So far I have designed the
following tables but I am unsure if I am handling the Clustering correctly.
The information I have been given is as follows -
A server can be standalone on a piece of hardware.
A server can be a virtual server in a cluster. Many virutual servers can
reside in a cluster. A cluster can have many pieces of hardware.
I have designed the following tables -
TblServer -
Server Id
Server Name
Description
Hardware Id
Cluster Id
TblHardware -
Hardware Id
Serial Number
Manufacturer
Server Id
TblClusters -
Cluster Id
Cluster name
TbleServerSoftware -
Server software Id
Server Id
Software Id
Date Installed
tble Software - (reference table)
Software Id
Software name
Software Version
Vendor


I have set up a 1 to many relationship between Hardware and Server
I have set up a 1 to many relationship between Clusters and Hardware

When entering datain the Servers table a server can either have a Hardware
Id or a Cluster Id but not both.

I am hoping that someone can tell me if I have designed the database
correctly or if their is a better way of doing this.
Thankyou