Creating My First DataBase Critique

Guys,
I just want to express my appreciation to you all.
Please do not hesitate to correct me when I am wrong.
I do not understand everything you guys are saying and have to look up the lingo, lol.

Just saying I appreciate you guys taking your time to help and educate me on this thing!

Cheers!
Kyp
 
Yes, images can be stored in a folder then complete or partial path to file can be saved in a text field. Images can be dynamically displayed on form or report with Image control, as long as they are not PDF type. Set Image control ControlSource property to the path field or path can be constructed with an expression, like: ="\\server name\folder path\" & [field with image name]

I provided a sample of different structure for master lookup table in post #13.

If an event can have only one Asset then I think your structure is appropriate.
 
  • Like
Reactions: Kyp
I've changed a few things up and added a couple records in each table.
I am not sure how to split up tblMasterLookup to make it correct. Splitting the table into multiple tables was what I originally had but seems like too many tables.
I may just be overthinking it?

I haven't figured out (yet but researching) how to populate the facility text box on frmEvent from the asset number in the combo box.

Here is the db, please critique.

Cheers!
Does 1 Event only ever have 1 Asset ?
 
Yes, images can be stored in a folder then complete or partial path to file can be saved in a text field. Images can be dynamically displayed on form or report with Image control, as long as they are not PDF type. Set Image control ControlSource property to the path field or path can be constructed with an expression, like: ="\\server name\folder path\" & [field with image name]

I provided a sample of different structure for master lookup table in post #13.

If an event can have only one Asset then I think your structure is appropriate.
Yes sir. I downloaded the sample you provided and studying it. I’m sure I will have question….
 
If that is true, then the relationship is correct and should not be changed.
Also, I just realized that facilities often have and asset with the same asset name, but different facility. I was also thinking about another scenario. In the asset table, the asset number PT-2100 is a pressure transmitter that resides on a pump. The pump would be the parent asset of
PT-2100. The pump (parent asset) can have upwards of 50 different assets.
Example. The parent asset “Pump” is the size of a small house, literally. On this pump, there are pressure devices, temperature devices, flow devices and a monstrosity of safety devices. I’m using the term asset in leu of device.
Should I rethink the overall structure and or relationships and start with the parent asset, then relate all the assets to the parent asset?
I think I just open myself a can of worms!
Thanks!
 
You can just redefine your relationships as follows and possibly think of better Table descriptions.
 

Attachments

  • ER.JPG
    ER.JPG
    90.7 KB · Views: 163
I thought this application was about events. Now it sounds like it is about assets. We're going to have to know more about the object of the application to advise on the schema.
I’m sorry.
I went back and reread all the comments from my first and second thread and made me rethink what I’m trying to accomplish.
The over all goal it to track assets, and asset events for a couple different purposes.
1. Track the history of an asset.
2. To have the asset info readily available.
3. Reporting the relevant info. ie down time, reliability and so forth.

I figured just the actual asset (device) would have been good enough however, after looking at a couple of spreadsheets I have been using for this, I realized that asset (AssetNumber) PT-2100 is an asset on multiple facilities, but the parent asset is different. The AssetNumber cannot be changed as the nomenclature of an asset is assigned by engineering, at the facility level.
Again, I am sorry for all the confusion.
At this point, what I think I need to do is write up a synopsis of how things are currently being tracked and logged in my worksheets, and start over with the overall idea of the db listing all relative info and the target end goal of the db in the synopsis. I guess I should have done this first…
 
Assets are like parts. You would have an Asset table that holds all the fields that define the asset. You have a Facilities table that defines the facilities. Then you need a junction table that connects assets to a facility. If the assets have serial numbers and installation dates, and other attributes that relate to its relationship to facility, those attributes would be stored in the junction table.

I'm not sure you need a bill of material (assets attached to other assets) unless something about the asset changes when it is attached to a different asset. For example, does something about a hard drive change if it is installed on a different computer? When you say that HDA is installed on Comp1, you specify the serial number for HDA. If you have a second computer with the same typ0e of HD, then it has a different serial number in the record that attaches it to Comp2.

If an asset requires different types of maintenance such as oil change, lubrication, rotation, etc, you would have a a table that defines Maintenance with a row for each type of maintenance for each asset. Then you would have a completed maintenance table that connects a maintenance type record to a SPECIFIC instance of an asset by linking it to the junction table that connects assets to facilities.
I agree with what you are saying but, since I have looked over my spreadsheets and reports, the parent asset is what’s described as the main topic of an event, and the child asset would be the cause.

Example of a current event report line: EN-873 (facility) PAX-7100 (parent asset) shut down due to a malfunctioning PT-2100 (device) and the relative comments, occurrences and descriptions would follow. The down time of the parent asset is being tracked for reliability purposes.

While all assets have serial numbers, some of them are illegible due to age and will have to rely on a tech at a different facility to add new assets to the list, (always adding or removing assets) rest assured the tech will not put in the serial number. I’m going to have to make it fool proof so that things don’t get all messed up. In the back of my mind I’m thinking cascading combo boxes cbo1 = facility, cbo2 = parent asset, cbo3 = device. Then the pertinent info can follow in the event report.

Thank you ma’am for taking your time to discuss this with me. I hope I am not being difficult. I apologize for my ignorance with this.
 
Might look at the Assets database template. I use Access 2010 and this is included in the templates collection viewable when opening Access application. But unfortunately, looks like download no longer supported for Access 2010.
 
Last edited:
Then perhaps to avoid confusion, you have Assets and Components. Logically, you will have problems if you have two entities that can have the same actions. If you go with Assets and components and all the action happens to a component, that will work but you will be creating a logical conundrum if you try to relate both Assets and Components to actions. You can make the Assets/Components division work even when not all Assets are broken down into components by creating a "dummy" component for all assets that don't naturally have any and relating your actions to the component level.

The change you made to convert all your "lookups" to be embedded in the table will now come back to haunt you because some will now actually have to become tables.
😡
Okay, I will just need to start over I guess.
thanks👍
 
You are the subject matter expert. I'm just making comments based on general world knowledge plus over 50 years of actual database design and implementation. It is not always easy to come up with a working schema. I understand why your first post was Excel but I usually just use Excel to collect lists of attributes next to table names as I assign them where I think they belong. Once past the first cut, I switch to Access where I can make relationships because tying everything together is extremely important and can help refine your thinking. In the early days before we had Access and PC's, my team used to use a big whiteboard or even a wall and tape up column names and move them around.
I’m still here and I haven’t given up!
 
I'm giving the table structure and relationships another try on a simple db.
I'm pulling my hair out trying to figure this out. Even after reading a lot, I still get confused....
Hope I at least have the structure right.....
 

Attachments

Hi
I would imagine that a Facility would have Many Employees.

Your method of adding more than 1 Employee in the field "AssignedEmployees" is the wrong way to do this.

See the ER Diagram attached. You need a separate table to record "FacilityEmployees"
 

Attachments

  • ER.JPG
    ER.JPG
    93.6 KB · Views: 153
  • Like
Reactions: Kyp
Hi
I would imagine that a Facility would have Many Employees.

Your method of adding more than 1 Employee in the field "AssignedEmployees" is the wrong way to do this.

See the ER Diagram attached. You need a separate table to record "FacilityEmployees"
Gotcha, I added tblAssignedEmployees and recreated the relationship as seen in your screenshot. In tblFacilities, I changed the datatype of "Assigned Employees" to number to agree with the relationship. With this change, would I be able to enter more than one employee in this field?
 
No you need to delete the field "AssignedEmployees" from the Facilities table.
 
Gotcha, I added tblAssignedEmployees and recreated the relationship as seen in your screenshot. In tblFacilities, I changed the datatype of "Assigned Employees" to number to agree with the relationship. With this change, would I be able to enter more than one employee in this field?
Can you upload your current database in zipped format?
 
  • Like
Reactions: Kyp
OK so does tblFacility only ever have :-

1 Contractor & 1 Controller
 

Users who are viewing this thread

Back
Top Bottom