Please help with table design

[DetRich]

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

 

[Bernard Peek]

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.

 

[Keith Wilby]

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

 

[DetRich]

Working on exactly that...

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

 

[rich]

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]

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.