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
|
|||
|
|||
Please help with table design
Hello,
I am building an Access application in which I need to track maintenance activity across our servers. My database currently has a master table with all the servers and related info (i.e. Hostname, IP addr, FQDN, etc.) For the maintanance activity, I'd like to accomplish the following tasks: 1. Be able to enter newly scheduled maintenance activities. 2. Be able to assign or associate each maintenance activity with 1 or more servers. 3. Be able to add notes and update the status of each maintenace activity. 4. View the maintenance activity for any specified server (maintenance history). 5. There may be other specific tasks, but hopefully, you get the idea. I am really struggling with the table designs. This seems like a situation where many-to-many relationships will occur and I really struggle with this. Maybe someone can build on this and get my creativity flowing... tblMaintenanceActivity 1. MaintActivityID: This is a unique number assigned to the maintenance activity. 2. ScheduledToOccur: DateTime stamp of when the activity will begin. 3. MaintenanceDuration: Shceduled window. Example: 1 hour. 4. Description: Description of the maintenance task. Maybe a memo field. 5. Status: Current status of this activity (i.e. scheduld, In progress, postponed, completed, etc.) 6. PerformedBy: The person who implements the activity In an effort to minimize the time/effort to enter data, on the form where the maintenance activity info is entered, I would also like to be able to select each server to which the activity applies. Thanks in advance, DetRich |
#2
|
|||
|
|||
Please help with table design
In message , DetRich
writes Hello, I am building an Access application in which I need to track maintenance activity across our servers. My database currently has a master table with all the servers and related info (i.e. Hostname, IP addr, FQDN, etc.) For the maintanance activity, I'd like to accomplish the following tasks: 1. Be able to enter newly scheduled maintenance activities. 2. Be able to assign or associate each maintenance activity with 1 or more servers. 3. Be able to add notes and update the status of each maintenace activity. 4. View the maintenance activity for any specified server (maintenance history). 5. There may be other specific tasks, but hopefully, you get the idea. I am really struggling with the table designs. This seems like a situation where many-to-many relationships will occur and I really struggle with this. Yes, it does look as if you need a many to many relationship but this needn't be too difficult to do. You need a table identifying servers. This table needs a primary key. That could be an autonumber or could be something like the FQDN. You need to consider what swapping a server would do to your data structure. You need a table for maintenance events. This table also needs a primary key which is likely to be an autonumber field. To manage the many to many relationship you need a third table which will connect the maintenance event to each server it deals with. This table needs two foreign key fields. These are the primary keys from the server and event tables. Maybe someone can build on this and get my creativity flowing... tblMaintenanceActivity 1. MaintActivityID: This is a unique number assigned to the maintenance activity. 2. ScheduledToOccur: DateTime stamp of when the activity will begin. 3. MaintenanceDuration: Shceduled window. Example: 1 hour. A question: If you have a maintenance event that deals with more than one server do you want to schedule work on each server separately or are you OK with allocating a block of time large enough to complete the task for all of the servers affected? 4. Description: Description of the maintenance task. Maybe a memo field. 5. Status: Current status of this activity (i.e. scheduld, In progress, postponed, completed, etc.) 6. PerformedBy: The person who implements the activity If you use this field here it means that only one person can be associated with the activity. What happens if you schedule a task to be completed by two people who each work on a server? This is where you need to take a close look at the link table you have created. You may find that some of your data fields belong there instead of either the event or server tables. In an effort to minimize the time/effort to enter data, on the form where the maintenance activity info is entered, I would also like to be able to select each server to which the activity applies. You can create a form for the maintenance activity. On it you can include a combo box populated from the server table. Use a button to create an entry in the linking table that includes the key from the current maintenance activity and the key from that combo box. This is just one way of doing the job, there are others. -- Bernard Peek |
#3
|
|||
|
|||
Please help with table design
"DetRich" wrote in message
... Hello, I am building an Access application in which I need to track maintenance activity across our servers. My database currently has a master table with all the servers and related info (i.e. Hostname, IP addr, FQDN, etc.) Sometimes I find it helpful to sketch out (on paper) what I want the data input form(s) and sub-form(s) to look like and build the tables and relationships to suit. Any mileage in that for you? Keith. www.keithwilby.co.uk |
#4
|
|||
|
|||
Please help with table design
Working on exactly that...
"Keith Wilby" wrote: "DetRich" wrote in message ... Hello, I am building an Access application in which I need to track maintenance activity across our servers. My database currently has a master table with all the servers and related info (i.e. Hostname, IP addr, FQDN, etc.) Sometimes I find it helpful to sketch out (on paper) what I want the data input form(s) and sub-form(s) to look like and build the tables and relationships to suit. Any mileage in that for you? Keith. www.keithwilby.co.uk |
#5
|
|||
|
|||
Please help with table design
Hello Bernard,
Typically, a single maintenance activity will be applied to multiple servers. For example, deploying anti-virus updates to 10 servers. Also, typically during normally scheduled 6-hour maintenance window over the weekend. So, there is a large block of time to complete all servers. Typically, one person will perform whatever maintenance is required. Even if there are multiple people, only one name is required. There is a seperate table with usernames so in the maintenance activity table, the name will come from the users table. For the form, I visualize a form/sub-form. The main form will have details of the maintenance activity and (I'm a little fuzzy on how to actually do this) the sub-form MAY have all the servers my team is responsible for. Each server MAY have a checkbox to indicate that the activity applies to this server. This is where I struggle to visualize it. So, I have a 1-to-many relationship (1 maint. activity to many servers). Will I end up with many records reflecting the 1-to-many, or something else? "Bernard Peek" wrote: In message , DetRich writes Hello, I am building an Access application in which I need to track maintenance activity across our servers. My database currently has a master table with all the servers and related info (i.e. Hostname, IP addr, FQDN, etc.) For the maintanance activity, I'd like to accomplish the following tasks: 1. Be able to enter newly scheduled maintenance activities. 2. Be able to assign or associate each maintenance activity with 1 or more servers. 3. Be able to add notes and update the status of each maintenace activity. 4. View the maintenance activity for any specified server (maintenance history). 5. There may be other specific tasks, but hopefully, you get the idea. I am really struggling with the table designs. This seems like a situation where many-to-many relationships will occur and I really struggle with this. Yes, it does look as if you need a many to many relationship but this needn't be too difficult to do. You need a table identifying servers. This table needs a primary key. That could be an autonumber or could be something like the FQDN. You need to consider what swapping a server would do to your data structure. You need a table for maintenance events. This table also needs a primary key which is likely to be an autonumber field. To manage the many to many relationship you need a third table which will connect the maintenance event to each server it deals with. This table needs two foreign key fields. These are the primary keys from the server and event tables. Maybe someone can build on this and get my creativity flowing... tblMaintenanceActivity 1. MaintActivityID: This is a unique number assigned to the maintenance activity. 2. ScheduledToOccur: DateTime stamp of when the activity will begin. 3. MaintenanceDuration: Shceduled window. Example: 1 hour. A question: If you have a maintenance event that deals with more than one server do you want to schedule work on each server separately or are you OK with allocating a block of time large enough to complete the task for all of the servers affected? 4. Description: Description of the maintenance task. Maybe a memo field. 5. Status: Current status of this activity (i.e. scheduld, In progress, postponed, completed, etc.) 6. PerformedBy: The person who implements the activity If you use this field here it means that only one person can be associated with the activity. What happens if you schedule a task to be completed by two people who each work on a server? This is where you need to take a close look at the link table you have created. You may find that some of your data fields belong there instead of either the event or server tables. In an effort to minimize the time/effort to enter data, on the form where the maintenance activity info is entered, I would also like to be able to select each server to which the activity applies. You can create a form for the maintenance activity. On it you can include a combo box populated from the server table. Use a button to create an entry in the linking table that includes the key from the current maintenance activity and the key from that combo box. This is just one way of doing the job, there are others. -- Bernard Peek |
#6
|
|||
|
|||
Please help with table design
In message , rich
writes Hello Bernard, Typically, a single maintenance activity will be applied to multiple servers. For example, deploying anti-virus updates to 10 servers. Also, typically during normally scheduled 6-hour maintenance window over the weekend. So, there is a large block of time to complete all servers. OK. Typically, one person will perform whatever maintenance is required. Even if there are multiple people, only one name is required. There is a seperate table with usernames so in the maintenance activity table, the name will come from the users table. OK For the form, I visualize a form/sub-form. The main form will have details of the maintenance activity and (I'm a little fuzzy on how to actually do this) the sub-form MAY have all the servers my team is responsible for. Each server MAY have a checkbox to indicate that the activity applies to this server. You could put a separate checkbox on the form for each server, but you would need to redesign the form when the server estate changes. This is where I struggle to visualize it. So, I have a 1-to-many relationship (1 maint. activity to many servers). Will I end up with many records reflecting the 1-to-many, or something else? Each maintenance activity will be associated with multiple records in the linking table. Each of those records would be linked to one entry in the server table. Symmetrically, each server record is linked to multiple records in the linking table. Each of those records is linked to one maintenance activity. With the three table structure you can have a form for maintenance activities. The subform would show which servers the activity applies to. You can also have a form listing servers. Its subform would list all of the maintenance activities for the selected server. -- Bernard Peek |
Thread Tools | |
Display Modes | |
|
|