Asset tracking database design question

[ittechguy]

I've been building a database to keep track of our communications equipment. Its come a long ways and I've learned a ton. But I'm starting to have doubts about how I've setup the relationships for tblEquipment and the related lookup tables. You can see the current relationships below.



Currently the database is only used for Cisco Switches, but I've been trying to set it up so that it can handle more. That is why I have the lookup tables, one for brand, one for type (switch, router, etc), and one for the software version.

After I populated data in the database, I've noticed several problems. First, I seemed to have entirely forgot to include model numbers.



Secondly, I don't seem to have an easy way of distinguishing between a logical switch and a physical switch. Most cisco switches, there is only one host name (EquipmentName) and only one IP address per switch. However, the Cisco 3750 switches can be stacked together so that several switches can logically behave as one switch. They all have one IP address and one host name, but they can have different model numbers and different serial numbers. So, if I wanted to look in a form to find information about one logical switch, I would not want to see 5 switches and get confused and start thinking that they're different switches when technically, they're the same. However, if I was looking at a form for asset tracking, I do want to see all 5 switches.

Another problem is that some switches have one chassis with its own model number and then several "blades" which fit into slots inside of that switch. Currently I have no way of keeping track of these blades. Its like I might need a table for "sub equipment" or something of the sort. But thats confusing to me because not all switches can have blades in them, and not all switches can be stacked.


...What should I be doing differently?

[NTC]

I would recommend you merge equipment, equipment type and brand into a single flat table....and include the software revision value field as a lookup type to the software table. That equipment table will certainly be a very big list.

As far as blades vs chassis - it can go either way; one could have blades as a sub table to its chassis; this is a hassle in terms of display needing sub forms - but it does better allow prevention of selecting the wrong blade for the chassis. On the other hand if the user knows what they are doing they can all be entries into Equipment and presume the user knows to select the correct components.

You will need a table (subtable) for software revision - that links to the equipment table - - using a lookup field type will work fine - - so that you select the Equipment XYZ123 and then select its software version....

[ittechguy]

Thank you for the reply.

I thought I needed to build more tables so that there was not duplicate values in the table(?). for an example, so "cisco" wasn't repeated 300 times.

I'm more inclined to have a sub table for blades. I'm just not sure how to lay it all out. As I said, some switches don't have blades.

I still have the problem of logical vs physical.

[ItsMe]

I would break off one issue and try to get it squared away, first. I would probably go after the Model thing. Since you are going for a separate table for Manufacturers, I would also go with a separate table for models. In tblModels, I would include a PK of Autonumber, FK from tblEquipmentBrand, and text for Description or ModelName. In the end, you will need a separate User Interface to manage Equipment brands and models. With these tables populated, the user's selection of a model could update tblEquipment with the EquipmentBrand and Model.

With that tested and out of the way, I might look to the issue of stacked switches. I would consider a Boolean in tblEquipment to indicate the hardware is stacked, maybe a Yes/No named IsStacked. With the Boolean implemented, you could duplicate IP values. Alternatively, you could use a default value or Null in the IP that would indicate IsStacked. A separate table would manage details about hardware that is grouped because of a logical stack. Maybe this would be a junction table but it may not have to be.

[ittechguy]

Thanks for the reply ItsMe!

I agree its best to start w/ model numbers. I've given this a lot of thought and also taken what's been said in this post into consideration. I've built this layout here:



It gets confusing as access never seems to do a good job displaying relationships. I hope I'm doing something wrong and I've added a few too many tables, because thats a lot, I feel.

I'll try to explain it.

tblEquipment contains:

CabinetFK --This links the equipment to cabinets (which then links it to rooms)
EquipmentStatusFK --Links equipment to tblEquipmentStatus, which stores current status of the equipment. Operational, In Storage, Discontinued, etc. For record keeping/audit purposes.
ModelNumFK --Links equipment to tblModelNum.
EquipmentNetworkType --Links equipment to tblEquipmentNetworkType, which displays whether the switch/router is a Core switch, distribution layer, etc. etc.
EquipmentName --Displays the logical hostname of the equipment. This is unique unless the switch is stacked (not sure how to specify that)
EquipmentIP --Displays the logical IP address of the equipment. Again, unique unless switch is stacked.
SoftwareVersionFK --Links to tblSoftwareVersion, which displays the current version of software on the equipment.
SerialNum --Serial number of the equipment. I did not have a separate table for this because the values will always be unique.
DateInstalled --The date the equipment was installed. Can be blank if equipment is in storage
DateUninstalled --The date the equipment was uninstalled. Can be blank if not yet uninstalled.

As for my sub tables and lookup tables, I have:

tblSubEquipment --Used to keep track of power supplies, cards, and other equipment which exists inside certain routers/switches. I may change the field names.

The fields are very similar to tblEquipment. After typing all this out, I realize I need to delete the link between tblSubEquipment and tblEquipmentBrand as that is covered in under CardModelNum.

tblModelNum --Contains model unique model numbers of equipment which we use. It also contains the brand and type (router, switch, etc). I did not include serial number because then it would not be unique.

tblCardModelNum --Similar to above. Will likely change the name to tblSubModelNum.

tblSoftwareVersion --Stores software version info. I linked it to tblEquipmentBrand because I believe it should list what brand goes to which software version(?).



Does all that make sense? What should I change, if any?

[ItsMe]

When I look at the example here I see a very very high level overview. Within, there are several entities that will require their own little database, of sorts. One example might be the Cabinet. You could, possibly, dedicate several tables and a single ERD to managing Cabinets. Similarly, you can dedicate several tables and a single ERD to managing Models. You have to create an abstract idea and get all of the small details for this idea on paper. Later, you can start to connect your various abstract ideas. I do not know of another way to introduce the level of referential integrity you seem to be after.

From a high level, start by distinguishing an Entity, like tblEquipment. Right away, you will notice this is a dynamic and complex entity. As with any entity, I would start to distinguish its attributes. I would distinguish Foreign Key attributes from others. In your case, you should recognize that some of these foreign keys can be merged together (kinda). As an example, a Model can Functionally Determine the Manufacturer. So you do not need an FK for manufacturer and for model in your tblEquipment.

I am really oversimplifying things here. However, the idea is to create a separate diagram and build a functioning model of your abstraction. In the diagram below you can see the beginning of something. If you use this diagram to build tables and queries, then interact with the queries, you might discover other attributes listed within tblEquipment that Functionally Determines the manufacturer. After testing the theory in the diagram, revisit and implement the method(s) needed within your application to manage the attributes defined within tblEquipment.


So you are doing what I am describing, up to the point of testing the theory in the diagram. I think you might discover something in the implementation, something you might be able to apply to other entities like Chassis. Attributes like serial number will likely fit into tblEquipment as a Text field. However, an attribute like SoftwareVersion or EquipmentType might Functionally Determine another Entity.

[ItsMe]

If you want to try an ERD tool. I use Lucid. You can start for free and then, after the trial, you pay. The only thing I found difficult with Lucid is creating an ERD specific diagram because they have many options. So I would have to hunt the sidebar of tools for the proper tool to create an ERD.
https://www.lucidchart.com/pages/tour/ER_diagram_tool

As you move further along, you might find some of the following terms useful.
Tuple
Normal Form
https://www.youtube.com/watch?v=7-Ek...7870F94CDE8F21

Functional Dependency
https://www.youtube.com/watch?v=i4sk4h2lhtU

Minimal Cover
https://www.youtube.com/watch?v=lKYz5e7INTg

Gary Boetticher Videos
https://www.youtube.com/user/GaryBoetticher/videos

[ittechguy]

Thanks for the help! I'm glad it seems I'm on the right track. I will look at those links when I get time later today.

I do have one question. I'm trying to build an audit log for my database. Do you think its better to have a separate (or perhaps 2-3) table(s) used for the audit log, or use the method from my previous post of including a installed date, uninstalled date, and equipment status field in the existing table and then never deleting a record? I've heard it both ways. Seems those who recommend I not create another table, are saying so because of the duplicate data it'd cause and then how it'd increase the size of the database. Which, atm I'm not too worried about that.

[ItsMe]

For what you are trying to do, I would use Booleans to determine the status of a given record. Another thing that can determine the status of a record is a Timestamp or the simple fact that a relative record exists. I would definitely have historical records for all acquisitions of equipment and I would have to exam the need to persist each IP assignment. In other words, some changes will be in the form of an append while others will be an UPDATE action. It would be difficult to come up with a reason not to overwrite an IP address assignment. Although, you never know. If you could find a reason, this would likely be managed via the application and not within the DB. An oversimplified explanation would be, the persistent layer updates the IP address, then the application layer logs the event elsewhere.

If there turns out to be a need to log something that the persistent data does not contain, you can always implement a separate log file. Nowadays, there are all sorts of options for storage. You could log stuff in a text file using fixed width, another DB, etc. It would be likely to log something that is not part of operations. One example would be logging Run Time Exceptions. It is suitable to use a log file for such a thing.

I feel it is more simple to identify business rules that may demand the need for historical data. Keep these historical records in the main DB. For instance, when a User of the application is entering something organically foreign, give the User a choice to back out and delete their data. No harm no foul if the user never commits to the changes. If the same User commits their changes as persistent data, do not offer a choice to delete/erase this data.

In order to know for sure what is the correct way and what is the incorrect way, you will have to try and see if it works. When you discover the answer for a particular implementation, you can use that experience to draw a conclusion for the next implementation. After a while, you can reflect on the various implementations and point a finger at the ones you got right as well as the ones you got wrong.

[ssanfu]

Consider:

(Post #5)
EquipmentStatusFK --Links equipment to tblEquipmentStatus, which stores current status of the equipment. Operational, In Storage, Discontinued, etc. For record keeping/audit purposes.

(Post #8)
I'm trying to build an audit log for my database. Do you think its better to have a separate (or perhaps 2-3) table(s) used for the audit log, or use the method from my previous post of including a installed date, uninstalled date, and equipment status field in the existing table and then never deleting a record?

Looking at the relationship between tblEquiptmentStatus & tblEquiptment, you could add a junction table to (track history) self document changes. (aka audit)
Delete field "EquiptmentStatusFK" from tblEquiptment and add table


jncEquipEquipStatus (jnc = junction)
-------------------
EquEquStatusPK........ Autonumber (I always have a autonumber field)
EquiptmentFK........... Long (link to tblEquiptment.EquiptmentPK ..... /see below/ or tblSubEquiptment.tblEquiptmentPK)
EquiptmentStatusFK.. Long
EquipChangeDate...... Date/Time (tracks when the status changed)


Might be able to use the junction table for tblSubEquiptment also.
Just add another field,
MainSubEquiptFK................ Long (Link to tblMainSubEquipt.MainSubEquiptPK)

tblMainSubEquipt
-----------
MainSubEquiptPK....... Autonumber
EquiptLevel............... Text (1 = Main, 2 = Sub)


How often does/could the Software version change? Might look at a junction table there also.



=======================================
One thing I don't like is some of your field names:
tblSubEquiptment.tblEquiptmentPK ---> having "tbl" as a prefix for a field name is...... well, just wrong.
tblCardModelNum.tblCardModelNumPK
tblModelNum.tblModelNumPK
tblEquiptmentStatus.tblEquiptmentStatusPK


My $0.02............