Mobile Data Equipment Inventory - Fire Department

[mduplantis]

Hi,

I have been working on the design of a small inventory database for our mobile data equipment. Along with the inventory, I am tracking service and maintenance. I believe I have a good idea on how to normalize the table, but one thing that is escaping me is how to handle the multiple tables of the different equipment that have dissimilar attributes. Many of the different pieces of equipment contains the same attributes such as serial number, make, model, ect. But, most have unique attributes that are not related to each other and are better stored with their type of equipment.

I have found several post and read different ways to handle this with one way creating a main inventory.device table which would store a unique ID for each piece of equipment. Using this main table, I can create a junction table to assign the equipment to an fire truck or service/maintenance table. If I don't use some type of main table for the inventory, I would need to create a junction table for each type of equipment, which I am certain is wrong.

Inventory.Device table
inventory_id
Device_Type (GPS, Dock, Mobile Computer)
Date_Purchased
Cost
Date_Retired


(how to link this table to my child tables)


GPS_Unit table
GPS_ID
Serial_Num
Model

Dock table
Dock_ID
Serial_Num
Model
Order_Num
Carton_Num

Mobile_Computer table
Computer_ID
Serial_Num
Make
Model
IP_Address


I have looked the sample databases (link provided in many post), but none seem to address the issue (at least that I can make out) of storing inventory with different types of equipment and linking back to a main inventory table.

Any links, idea are diagrams would be greatly appreciated.

Thanks,
Brent

[ItsMe]

I cannot provide a simple explanation to such a complex problem. I would start by asking yourself what the purpose of the Inventory table is. Ask yourself, "Why do I need to have a Cost attribute?" Ask the same question for each of the attributes. What is it about the business rules that caused you to consider Cost an attribute to the Inventory entity. The idea here is to further define the entities. Scrutinize and distill each entity and each attribute.

You can do a similar thing to the other entities you have displayed here. Compare the attributes of each entity. Ask yourself, "Why do I have SerialNum as an attribute and why is it defined in each of the three tables?" The SerialNum attribute seems to be a better attribute to Inventory than to GpsUnit or DockTable.

After defining an attribute like SerialNum and realizing it is best to apply this attribute to the parent entity, I would use the idea, the concept, of the SerialNum attribute and how this attribute is associated with the Inventory entity as I moved to define other attributes of Inventory. I would also revisit the question of why I have Cost as an attribute of Inventory.

When you examine the business rules associated with Cost, do they compare to SerialNum? They might, but this is the process of conceptualizing the entities and diagraming them. So I would start to explore what the business is going to do with Cost. Does the business need the initial cost of the item as well as other costs? What about tax amount paid, assessed value, market value, replacement value, borrowed amount, grant amount, etc.? Determine what is an attribute and what is an entity so you can answer your questions regarding Relations.

[mduplantis]

Hi,

yes, that make sense. I had a serial number in each child table so I could identify which GPS unit for each record, but I see what you are saying. I can place all the common attributes in the inventory table, leaving only the unlike items in each child table. I would then identify the record using the record source as query and hiding the key columns. But, that still leaves me confused on how to link the parent inventory table to the child tables. I tried adding a key from each child into the parent, but unless each column had a value, the parent record could not be saved.

Thanks

[ItsMe]

I tried adding a key from each child into the parent, but unless each column had a value, the parent record could not be saved.

I do not like to use Access to help me create an ERD. If I use the Relationships window to define a relationship, it will be difficult to impossible to test any theory I may have. In other words, the Relationships window is used to define constraints not to test an idea. I suggest you use paper and pencil and or a third party diagram tool. Lately, I have been using this tool
https://www.lucidchart.com/

In short, do not use the Relationships Window while developing your ERD. Do create tables in Access and use queries to test relationships in your ERD.

Another thing, post #2 is trying to get you to focus on your inventory Entity. You will continue to ask how to create a relationship between the Inventory entity and its children until you distill the entity, Inventory, and its attributes. Get the Inventory down to brass tacks and only include attributes that would, otherwise, be duplicated in each of the other three children entities.

[orange]

The concept of many devices have the same attributes, but each device type has its own specific attributes.
This is called Generalization/Specialization or SuperType/SubType.
There are youtube and other tutorials describing this.

This one has info at roughly the 45 min mark. You can fast forward to see the specifics, or the tutorial is all about modeling.

[ItsMe]

...
This one has info at roughly the 45 min mark...

Orange, I looked at some of the tutorials offered by Dr. Daniel Soper because of a link you provided in another thread. I believe Dr. Daniel Soper's playlist for Database Lessons should be used as a reference for anything database.

I have had a chance to watch some of his videos and I intend to watch more. I find them to be well done and I appreciate his emphasis on terms. I want to watch his videos so I can do a good job of referring others to this excellent resource and, also, so I can learn about database theory/development. I do not see why this resource should not be used as a 'standard'.

[orange]

ItsMe,

I don't disagree. I have looked for tutorials that I think tell the message and are suited to various audiences.
I used to recommend a series by Dr Art Langer -they weren't the best from video quality to content - but they dealt with the same subject and (to me) were consistent. I had a number of complaints on a different forum from too amateurish, no rigorous alignment to principles.... When you get readers who have a sound mathematical/academic orientation to set theory and sql, everything suited to the common poster who has no formal training is too trivial/misses the rigor etc. I still think the Langer videos are useful to many developers.

There are many audiences, and, as you have seen, many posters have no database training or experience. They have Access and they're intent on creating an efficient, production database on their first attempt.
So trying to match videos to a variety of situations will likely require a number of tutorials/videos etc.
Youtube wise, I like the Dr. Soper stuff, but I also would recommend most anything by Dr. Jennifer Widom. For Access I recommend Steve Bishop's Access Series. And here is a 26 minute video on a Stock Control Database application that I feel is great for anyone starting to design an inventory/stock control system. In half an hour you get a real introduction to issues and reality of such a system.

In many cases the newbies would benefit from videos on analysis, flow charts, naming conventions. There are some Langer videos on Analysis that are more than sufficient to get the basics.

There are also good, free youtube videos by 599CD on specific aspects/uses of Access. This series is quite good

[ItsMe]

Orange,

You make some very good points about matching a tutorial with an individual. There will always be a need for high level views. I have watched parts of the Stock Control Database and it seems like a good one. By the way, I did not notice a link to it.

Like you, I have tried to find good resources for Access specific tutorials. I have never been satisfied with any one series. Of course, I have not seen them all. However, before I refer someone to a tutorial, I try to understand the message within the tutorial. The messages I get from most Access tutorials seem to be messages of misinformation. The Stock Control video seems to be an exception to the rule.

I am not trying to say that every statement I have ever made regarding Access or any other technology has always been perfect or even correct. Simply, I am hesitant to refer people to many of these Access online video tutorials. The reality is that I do not have the time to review them all. From what I have seen, there are better and more accurate resources that are not specific to Access.

My take on it is, use tutorials that are specific to theory. This way, the pool of resources is larger and you have a better chance of learning the industry's 'best practices'. Also, use books published by reputable publishers to learn about the nuances of Microsoft Access.

These are the reasons I am so excited about Dr. Daniel Soper's tutorials. I also like the tutorials Dr. Art Langer and Jennifer Widom have provided. I wish I knew where to find more like these.

[mduplantis]

Hi,

Yes, I have used paper, pencil and Excel to help create the model. I was placing into Access to see if I could make it work. I have had 3 or 4 different models, trying to break the attributes down into a model that I could make work. I have distilled the inventory down to the very basic structures and the post was one of many ways I tried.

All your points are well made and I'll continue to go back to the drawing board.

Thanks

[mduplantis]

I belive I have watched several of his videos, but I will go to that reference and look it up. Thanks.

[mduplantis]

I will look at the videos as you suggested. I am familair with database modeling, but I am not familair with a more complex design such as the inventory with multiple different child like tables. I am certain there is a bettter approach and that's what I am searching for. I have written my design in text, identified the nouns and adverbs, determining the relation between each object. After that, I get confused and lost on how to make it all fit together.

Thank you. I'll check out your suggested videos. I also belong to allinonceaccess.com which is also an excellent resource.

Thanks

[ItsMe]

The reality is that Orange was spot on in defining the Inventory as a Supertype. If you have few hours to watch the Dr. Daniel Soper videos, they may help. I would urge you to keep the Inventory entity as pure as possible and do not add attributes that can stand alone, somewhere else in the model. You may discover that Inventory is not a very good name for this "Supertype". You may discover that the Inventory entity will take shape as something else and not be so closely tied to the GPSUnit, for instance.

BTW, it sounds like you are on the way to success, so keep applying pressure ...

[mduplantis]

Okay. Supertype (some type of hiearcy) was what I was after, so I will watch the video when I am off work. thanks!

[orange]

mduplantis,

Subtype/supertype is the concept involved. The video reference was for a tutorial to describe/illustrate the concept.
Here is another article from mvps.org (another great reference site) subclassing.
Review and see if they apply before making any decisions.

Good luck with your project.

[mduplantis]

Hello to All,

I went back and watched the entire video by Dr. Soper. It was an excellent resource with some of it being review, but quite a bit of it being new information on ER design. I had no knowledge of the supertype/subtype modeling and that is exactly what I was missing in the project's concept (along with inexperience and knowledge). I also downloaded Lucidart and I will go back and start over from scratch with all the new information.

I've watched the majority of the videos, at least in part, but Dr. Soper and Dr. Widom's are new resources that I have book marked to watch. At some point, I have found, it is better to at least try to create something, as opposed to just skipping from youtube video to youtube video.

Thanks to everyone for the comments, links and suggestions, which I will continue to refer back to as I develop the project. One thing is for sure, is that I totally underestimated the complexity of the project. The good news is that I am creating the database as a learning project and it will be used by just one person (co worker). I typically do more database reporting than design and it shows. But, I have a strong determination to make a good effort using at least the very basic of design principles as I can.

Again, Thanks.
Brent