I have been trying to figure this out for a couple of days, so any input that could be offered would be great.

A little over a year ago I started to work on an application not dissimilar from Spiceworks for use at my job. I'd have used the aforementioned but it was creating and infinite loop to itself that the Security team flagged and we found that its process on target machines was sometimes hammering the systems. As such I started to plan out a web interface, VB.NET app, and a database but due to time constraints all of this was back burnered. Now it is time for my capstone project for school and I am doing an expanded version of the original idea. However, that brings me to my database issue.

The premise of the project is to query, store, accept manual input, and generate reports about assets. The issue I run into is those assets include desktops, laptops, scan guns with CE, scan guns with various versions of Android, printers, UPS systems, IP addresses, and network hardware. As such there are a number of shared properties for each asset type and A LOT that are different as well. The differences are where I have a problem though, how do you break those out into tables in a relational database in a way that makes sense? Or can you? The best solution I have so far is that the asset table has to have ALL of the properties and some are allowed to be null. For instance, how would i relate properties of an asset such as CPU Load, temperature, current user, total memory, available memory, total HD, available HD, etc.

I am not at my laptop right now so don't have the visio document with the design on it, but in short evreything revolves around events.

The event table has a one to many relation with the event_type table.
The event table has a many to one relation with the asset table.
the asset table has a one to many relation with the asset_type table.

Again, if anyone has some input on this, it would be appreciated. I really do not want to do a non relational DB with independent tables for each asset type.

BFRD

03-14-2019 05:12 AM

Database normalization is obviously a complex topic and can be highly contested depending on to whom you are speaking. I like a common sense approach, normalize until it makes sense. So far from what you put down, you are on the right track. The properties do pose a challenge. What I would do is to figure out your property sets for each item and select the common properties. That list may not be large, but some properties will be common. I would be pretty generous here things like MAC addresses could be considered common. Looking at the Spiceworks app may give you some insight into the things they thought should be common. I would create non-null columns for each of these common properties. For the rest, consider using a single nvarchar(max) column. I am assuming MS SQL Server since you are using an MS dev stack. In this column, you can serialize an object containing all of the custom properties. I would probably go with a JSON serialization even for a desktop app. Newtonsoft is built into the framework and makes serialization and deserialization of objects incredibly simple. It does make querying against the custom values more difficult, but not impossible. I am not sure how often that would really be necessary anyway.

And now for some unsolicited advice...
Please use C# instead of VB.NET. I cannot think of a good reason to use VB.NET over C#. Learning the syntax is not difficult and will give you a better understanding of all c-based languages.

NameUnknown

03-16-2019 08:30 PM

Thank you for the reply on this. I had not really considered the idea of a single column containing an serialized object before. I will play around with that structure and see how it works out for me. It's been a while since I did any database work so this may be a rather long process.

As for VB.NET vs C#, the only reason I did VB.NET is I am more familiar with it so for quick mockups and examples it works really well. I am all for moving this to C# though given that it can be used for Android & iOS apps as well. Given this, what do you think about MS Universal apps & converting code from one platform to another in Visual Studio & Xamarin? Slight digression, but curious what your opinion is as well.

BFRD

03-21-2019 06:11 PM

Quote:

Originally Posted by NameUnknown
(Post 27895164)

Thank you for the reply on this. I had not really considered the idea of a single column containing an serialized object before. I will play around with that structure and see how it works out for me. It's been a while since I did any database work so this may be a rather long process.

As for VB.NET vs C#, the only reason I did VB.NET is I am more familiar with it so for quick mockups and examples it works really well. I am all for moving this to C# though given that it can be used for Android & iOS apps as well. Given this, what do you think about MS Universal apps & converting code from one platform to another in Visual Studio & Xamarin? Slight digression, but curious what your opinion is as well.

I suppose I don’t really have an opinion. All of my professional work are for business applications. While there are several businesses moving their apps to mobile platforms, ours can’t really go that direction.

For prototyping I can’t say enough about LinqPad. I do almost all of my prototypes there. I would absolutely check that out. Take their linqpad challenge. It isn’t everyone’s cup of tea, but it certainly makes my life easier.