If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Database Design Problem
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 |
#3
|
|||
|
|||
Database Design Problem
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 |
#4
|
|||
|
|||
Database Design Problem
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 |
Thread Tools | |
Display Modes | |
|
|