Creating My First DataBase Critique

[Kyp]

@Kyp,
I forgot to mention that your relationship between Event and Asset appears to be backwards unless I don't know what an asset is. If you can have more than one asset assigned to an Event, then you need a junction table between Event and Asset so you can assign multiple Assets to multiple events. You would remove AssitID from the Event table since that restricts you to a single asset for an event. Regardless, it is incorrect to have AssetNumber in Event. It should exist ONLY in the Asset table.

An event would be tied to only one asset while an asset can have multiple events (over time). With that being said, I need to read up on relationships. I will reverse it.

I would also not prefix all column names with table information. This just forces you to type more characters before useful intellisense kicks in and it hides the actual column name when you open the table in DS view or a query because all you see are the prefixes and maybe one or two characters of the actual column name.

I only did this as to not confuse myself. Opening the DS I do see what you are saying. I will fix this.

For column names that occur in multiple tables such as the address fields, I wouldn't worry about the "duplication". If you have to use the two tables in the same query, you can alias the names from one table using a prefix to disambiguate the reference.

I kind of understand what you're saying but I can't find where I used any duplication. Can you elaborate a little more on this please?

And finally, although there is nothing wrong with the Attachment data type per se, you need to be aware that it is not supported by SQL Server or other RDBMS' so if you ever have to upsize, you would need to not only convert the data manually, but also modify the app since you would need to handle OLE objects differently from Attachments. If you don't envision ever having to upgrade the BE, then the Attachment data type is fine although for images, I would still recommend storing the path rather than the object itself because Jet/ACE bloat easily when you embed objects. SQL Server is better equipped to handle OLE objects.

Once the db is split, the BE will be stored on the corp network in a shared drive accessible by everyone. In the same folder as the BE I want to store the attachments in a separate folder and as you stated, storing as a path rather than the image. This would dramatically increase the overall db size and will eventually exceed the 2GB limit. (I read this in a previous reply). My only issue is that I will have to learn how to accomplish this when the time comes.

 

[Kyp]

@plog - you make this recommendation quite frequently and I always respond with posting my table maintenance application which I'm going to do here also. What I don't understand is why? Why would you want the developer to individually manage the lookups? Why would you want to maintain the lookup lists in multiple places? Why would you not want to have the ability to deactivate a value? I don't use separate tables for each list because it adds a lot of work that I can avoid by using my table maintenance method.

I have down loaded your attachment and it seems that it would be easier to maintain all the data. Not sure I understand it all, but I'm trying!

 

[Kyp]

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

 

[June7]

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.

 

[mike60smart]

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 ?

 

[Kyp]

Does 1 Event only ever have 1 Asset ?

Yes, an event will only have one asset.

 

[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.

Yes sir. I downloaded the sample you provided and studying it. I’m sure I will have question….

 

[Pat Hartman]

An event would be tied to only one asset while an asset can have multiple events (over time). With that being said, I need to read up on relationships. I will reverse it.

If that is true, then the relationship is correct and should not be changed.

 

[Kyp]

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!

 

[mike60smart]

You can just redefine your relationships as follows and possibly think of better Table descriptions.

 

[Pat Hartman]

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.

 

[Kyp]

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…

 

[Pat Hartman]

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.

 

[Kyp]

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.

 

[June7]

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.

 

[Pat Hartman]

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.

 

[Kyp]

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

 

[Pat Hartman]

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.

 

[Kyp]

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!

 

[Kyp]

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.....