Databse Theory/Design

[RobTuby]

Hi

I have been asked to design and build a database to track maintenance and i have a few questions:

Scenario:
There are multiple things that need to be tracked/recorded.

TBL_Maintenance
-MaintenanceTaskNumber
-WorkCity
-WorkSite
-DueDate
-DescriptionOfTask
-ActionRequired
-AccessStatus
-EngineerStatus
-InspectorStatus
-OverallStatus (Unplanned, Access Booked,Access pending, Engineer Booked,Inspector Booked, Cancelled)
-UpdatedDate

An access booking must be made
TBL_Access
-MaintenanceTaskNumber
-AccessNumber
-AccessStatus (Approved,Pending,Not required)
-AccessStartDate
-AccessEndDate


An enginner needs to be assigned
TBL_Engineer
-MaintenanceTaskNumber
-EngineerName
-EngineerBookedDate (needs to be between AccessStart and AccessEnd)
-EngineerPhoneNumber


A supervisor/Inspector but be booked
TBL_Inspector
-MaintenanceTaskNumber
-InspectorName
-InspectorBookedDate (needs to be between AccessStart and AccessEnd)

actions and comments needed to be captured for reporting
TBL_Action
-MaintenanceTaskNumber
-Changetype ( Access Booked, Engineer Booked, Inspector Booked,Cancelled)
-Comments
-CancelledReasonCode
-ChangedBy
-ChangeDate


The database will have rolling data sent from an external Database as an excel file ( Once the work is completed it will re-output the new date to be done.)

on receiving the excel file it will need to check if the record already exists or create a new record

I was thinking that the (MaintenanceTaskNumber+DueDate) should be the unique key.

There needs to be a userforms from end that allows the planner record the bookings.

Some of the reports required are to show any unplanned, any overdue, Current state of planning, Time Taken to plan(each step)

Would someone kindly helping me check i have the correct logic and foundations of the database set correctly?

 

[MarkK]

I don't see any ID fields or relationships. The power of a relational database is in constructing a model of related data.

Say a customer has many orders. For each row in the order table there will be a field called CustomerID, so each order is "linked" in a one-to-many relationship with a customer.

How is your data related? What does a Maintenance record keep track of? Are there many Action records related to a Maintenance record?

I would store engineers and inspectors in the same table. I would not store booked date in the same table as the person being booked. There will be many bookings for one engineer, so bookings should be in a related table.

 

[RobTuby]

Thanks for your reply MarkK,

the relational field would be MaintenceTaskNumber.

the maintenanceTaskNumber is basically the order number.

the flow process is as follows which may help

Email with Attachment
Upload Attachment to DB
Macro/Queries check if record exists or creates new record.

Users

Selects Record,
checks system if access is required or not
If access is required then book the access.

Books Engineer (internal) and records booking date

Books Inspector (External person) and records date

The DB would record the booking and any changes to the booking once made( need to see how many times the bookings are rebooked and what reason for this to find the wastage)

 

[jdraw]

You may get ideas from these generic models.
http://www.databaseanswers.org/data_models/equipment_maintenance/index.htm
http://databaseanswers.org/data_models/vehicle_maintenance/index.htm

 

[Minty]

The way you ave it laid out at the moment looks okay until you want to rebook a cancelled visit, or you have to revisit due to a parts failure or lack of time etc .etc.

As Mark pointed out each of your related tables at the moment will only be a single record.

That won't work in practice. (I work for a maintenance company - trust me)

 

[MarkK]

The DB would record the booking

Where's the booking table?

 

[RobTuby]

I think that maybe what is missing, I've only used access as data storage before now so trying to build this is a challenge.

TBL_Booking
MaintenanceTaskID
TBL_EngineerID
TBL_InspectorID
TBL_AccessID
DateBooked
BookedBy
UpdateDate
InspectorStatus
AccessStatus
EngineerStatus

??

 

[RobTuby]

would someone be willin to assist in the basic set up as i'm getting confused?

 

[plog]

would someone be willin to assist in the basic set up as i'm getting confused?

Yes, my advice is to dive in. First start with a spreadsheet. In row 1 put field names for every field of data you need to capture. Then fill in some fake data underneath (or if you have real data, use that). No IDs, no other tables, just one big spreadsheet to show all the data you want. Make sure you account for every field you need. Keeping adding field names in row 1, until everything is accounted for.

The next step is to normalize that data in Access. Identify all the data that belongs together (Engineer Name, Engineer Phone Number, ...; ChangeType, ChangeComments, ChangedBy, ...) and make sperate tables for them. Move your data from the spreadsheet to your Access tables. Make sure the data makes sense and you can put it back together via queries.

Then when you are done moving your data to Access, look it over, make sure it seems right, write down any issues you think you have and then open up the Relationships tool and set your relationships among all your tables. Lastly, post a screenshot of the relationships back here, along with your issues and a simple explanation of what real-life process you are modeling. In that explanation, don't use any database jargon. Just pretend its career day for a 3rd grade class and explain to use what you do.

Then we will yell at you, say you are completely wrong and push you in the right direction.

 

[RobTuby]

Hi,

Sorry for the delay in replying

I have remade the database after following the above advice.

i have attached my efforts so far.

The purpose for the database for 3rd graders-

I have been asked to design an program/database that takes raw data, processes it, updates to MainTable.
When a planner books something they enter it in the database to capture it.
This then allows for reporting.

for Info ( not for 3rd Graders)

this is part of Asset management, sometimes an outage isn't required. if it is then the outage details are recorded. The engineer is an internal employee, the Inspector is External.

The Engineer and Inspector need to match, Also if an outage is required it must be be within the outage Dates.

Reference is made from Asset Number and DueDate.

I could do with some help in writing the first userform for submitted the information.

Also in the methodology in showing the latest status/booking details.

The best method of capturing the cancellation/moving reasons.

Thanks

Rob

 

[plog]

1. You've used ID fields, but you don't know why you did. Autonumber primary keys are used to make records unique in one table, and then as foreign keys (http://www.w3schools.com/sql/sql_foreignkey.asp) in another table to link the tables together. You've used your [Reference] field for this.

2. TBL_MainData is a poor table name. As an outsider I know TBL_Status contains status information, TBL_Comments has comments. TBL_MainData? No clue. I know its important, but it doesn't help me understand what data it contains.

3. You shouldn't have tables with the same structure. TBL_Inspector and TBL_Engineer shoudl have their data combined into the same table (TBL_Personnell), with a new field (most likely Yes/No) to determine if they are an Inspector or an Engineer.

4. TBL_Inspector and TBL_Engineer are misnamed. Looking at them and your relationships, a better name would be TBL_Assignments. Those tables assign which inspectors go with each record in TBL_MainData. You need a table to hold all personnell (TBL_Personnell) and also a table to sort out which personnell was assigned to each tbl_MainData (TBL_PersonnellAssignments).

5. You only have used Short Text fields. I'm pretty sure that a lot of field names sufffixed with '-Date' (InspectorBookedDate, InspectorLastUpdatedDate, etc.) will be storing dates. If that's the case, then you need to make them a Date/Time data type. Looks like you did the same thing for fields to hold numbers. Use the right data types.


Also,

The Engineer and Inspector need to match

What does that mean? They should be the same person? They always are assigned together such that Jim Smith always inspects when Steve Jones is the Engineer? Could you elaborate and define 'match?'

 

[jdraw]

RobTuby,

You may find that spending 45 minutes working through one of the tutorials from RogersAccessLibrary will pay great dividends in your database project. You will learn table design, normalization, and relationships by working through one of these:
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip
http://www.rogersaccesslibrary.com/Tutorials/ScripDesign.zip
http://www.rogersaccesslibrary.com/Tutorials/ConsolidatedWidgetsDesign.zip

Normalization
Good luck with your project.

 

[RobTuby]

1. You've used ID fields, but you don't know why you did. Autonumber primary keys are used to make records unique in one table, and then as foreign keys (http://www.w3schools.com/sql/sql_foreignkey.asp) in another table to link the tables together. You've used your [Reference] field for this.

2. TBL_MainData is a poor table name. As an outsider I know TBL_Status contains status information, TBL_Comments has comments. TBL_MainData? No clue. I know its important, but it doesn't help me understand what data it contains.

3. You shouldn't have tables with the same structure. TBL_Inspector and TBL_Engineer shoudl have their data combined into the same table (TBL_Personnell), with a new field (most likely Yes/No) to determine if they are an Inspector or an Engineer.

4. TBL_Inspector and TBL_Engineer are misnamed. Looking at them and your relationships, a better name would be TBL_Assignments. Those tables assign which inspectors go with each record in TBL_MainData. You need a table to hold all personnell (TBL_Personnell) and also a table to sort out which personnell was assigned to each tbl_MainData (TBL_PersonnellAssignments).

5. You only have used Short Text fields. I'm pretty sure that a lot of field names sufffixed with '-Date' (InspectorBookedDate, InspectorLastUpdatedDate, etc.) will be storing dates. If that's the case, then you need to make them a Date/Time data type. Looks like you did the same thing for fields to hold numbers. Use the right data types.

Also,

What does that mean? They should be the same person? They always are assigned together such that Jim Smith always inspects when Steve Jones is the Engineer? Could you elaborate and define 'match?'

Thank you for your reply

I think i have actioned point 1 correctly.

I have remaned the MainData to AssetData as that is what the table holds

i have deleted Enginner, Renamed the inspector to personnell and added a field for selecting engineer/inspector type, I am slightly confused around TBL_PersonnellAssignments?


I have changed the fields to dates. Some of the ones where it looks like it should be a number will need to stay as text as sometimes they contain a letter.

Where i said the engineer needs to match the inspector i was referring to the assignment date (PersonBookedDate).
The scenario os that during the outage both the engineer and inspector are booked to attend the asset to maintain/examine it.


i have attached the current version of the database with the changes made.
Query1 is what i used to create the relationships

Thank you for your support.

Regards

Rob

 

[plog]

You need a table of just Inspectors/Engineers data (TBL_Personnell) and that's it. TBL_Personnell will not be directly linked to TBL_Assets. It will indirectly be linked.

You need a junction table (https://en.wikipedia.org/wiki/Junction_table) between the two tables. This table (TBL_PersonellAssignments) will sort out who got assigned to what Asset and in what capacity (Engineer/Inspector).

TBL_Personnell will hold all the information about a person: First Name, Last Name, Phone Number, etc.

TBL_PersonnellAssignments will hold all the information about the assignment of a person to an asset: ID of Asset, ID of Person, Role, Booked Date, Updated By, etc.

I see you changed your relationships to use AssetID as a foreign key, which is good. However, just so there's no confusion I would change all those foreign fields from 'Reference' to AssetID. Also, does the Reference field in TBL_AssetData serve a purpose and is it unique? It looks like its a calculated field of AssetNumber and DueDate. You don't store redundant data in a database, so I think it should go.

 

[RobTuby]

You need a table of just Inspectors/Engineers data (TBL_Personnell) and that's it. TBL_Personnell will not be directly linked to TBL_Assets. It will indirectly be linked.

You need a junction table (https://en.wikipedia.org/wiki/Junction_table) between the two tables. This table (TBL_PersonellAssignments) will sort out who got assigned to what Asset and in what capacity (Engineer/Inspector).

TBL_Personnell will hold all the information about a person: First Name, Last Name, Phone Number, etc.

TBL_PersonnellAssignments will hold all the information about the assignment of a person to an asset: ID of Asset, ID of Person, Role, Booked Date, Updated By, etc.

I see you changed your relationships to use AssetID as a foreign key, which is good. However, just so there's no confusion I would change all those foreign fields from 'Reference' to AssetID. Also, does the Reference field in TBL_AssetData serve a purpose and is it unique? It looks like its a calculated field of AssetNumber and DueDate. You don't store redundant data in a database, so I think it should go.

Would you advise an TBL_Users as this field seems to be in all the tables?

 

[plog]

Yes, TBL_Users sounds good if you have more people than just Inspectors and Engineers.

 

[RobTuby]

Thanks, i have added that in,
My next concern is dealing with Dates within Access and the best method/approach.

One of my previos databases had a habit of randomly changing dates stored from dd/mm/yyyy to mm/dd/yyyy.

What is the best way of handling these?

Thanks

Rob

 

[plog]

You can explicitly define the Format for how they are stored, so that shouldn't be an issue: https://support.office.com/en-us/ar...c1-52fa-416a-b8d5-ba24d881b698#__toc260216069

I thought you were going to have just a TBL_Users instead of TBL_Personell. Please explain the difference between a user and personell.

In your relationships, you need to remove TBL_Users and all the extra TBL_AssetData tables.

Also, you really need to evaluate if you need all those LastUpdated fields. What's the purpose? And are they serving that purpose? If you truly need them (which I am suspect of), it might be better just to have a whole new table for those like this:

TBL_Updates
Update_ID, autonumber, primary key
ID_Updater, number, foreign key to TBL_Users/TBL_Personell of who did update
Update_Date, date, date update occured
Update_Table, text, name of table update occured on
Update_Record, number, foreign key to ID field of Update_Table to identify record updated

Then you just have one place to store all your updates. Further you could add an Update_Notes field which would detail the update that was made. Again, though, highly suspect that this type of data is needed.

 

[RobTuby]

Re: Database Theory/Design

Hi

The reason for the last updated by and UpdatedDate was so when i queried it would show the latest data.

How would i populate the UpdateTable Field to get the table name??

 

[plog]

You would have to know it. User's interact with data via forms. Forms are based on tables. So depending on what form a user was using, you would know what table they are interacting with. You could also use a Form Event (https://msdn.microsoft.com/en-us/library/office/jj249049.aspx?f=255&MSPPError=-2147217396) to capture the specific ID of the record they are working with.