Relational Database setup question - Overclock.net - An Overclocking Community

Forum Jump: 

Relational Database setup question

 
Thread Tools
post #1 of 4 (permalink) Old 03-12-2019, 09:23 PM - Thread Starter
New to Overclock.net
 
Join Date: Apr 2009
Location: Cincinnati Ohio
Posts: 4,405
Rep: 172 (Unique: 146)
Relational Database setup question

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.

. . .
Ever Evolving
(18 items)
CPU
Phenom II 1090T Black Edition
Motherboard
Gigabyte GA-890FXA-UD5
GPU
Diamond R9 280X
GPU
XFX R9 280X
RAM
Crucial Ballistix Sport 16GB VLP DDR3 1600 CL9 1.35v
Hard Drive
Vertex 3 120GB SSD
Hard Drive
2x WD 1TB Caviar Black SATA3
Hard Drive
WD Caviar Black 640GB SATA2
Optical Drive
HP DVD-RW 1170i
Power Supply
Corsair HX1000W
Cooling
Prolimatech Megahalem
Cooling
2x Delta AFB1212SHE
Cooling
7x Cooler Master SickleFlow R4 fans
Case
Cooler Master HAF932 (Fan Mod)
Operating System
Windows 10 Professional
Monitor
2x Samsung SyncMaster E2420 23.6" 1920x1080
Keyboard
Logitech G15
Mouse
Logitech MX310
▲ hide details ▲
NameUnknown is offline  
Sponsored Links
Advertisement
 
post #2 of 4 (permalink) Old 03-14-2019, 05:12 AM
Some call me... Bifford
 
BFRD's Avatar
 
Join Date: Dec 2004
Location: Carrollton, TX
Posts: 5,264
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.



BFRD is offline  
post #3 of 4 (permalink) Old 03-16-2019, 08:30 PM - Thread Starter
New to Overclock.net
 
Join Date: Apr 2009
Location: Cincinnati Ohio
Posts: 4,405
Rep: 172 (Unique: 146)
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.

. . .
Ever Evolving
(18 items)
CPU
Phenom II 1090T Black Edition
Motherboard
Gigabyte GA-890FXA-UD5
GPU
Diamond R9 280X
GPU
XFX R9 280X
RAM
Crucial Ballistix Sport 16GB VLP DDR3 1600 CL9 1.35v
Hard Drive
Vertex 3 120GB SSD
Hard Drive
2x WD 1TB Caviar Black SATA3
Hard Drive
WD Caviar Black 640GB SATA2
Optical Drive
HP DVD-RW 1170i
Power Supply
Corsair HX1000W
Cooling
Prolimatech Megahalem
Cooling
2x Delta AFB1212SHE
Cooling
7x Cooler Master SickleFlow R4 fans
Case
Cooler Master HAF932 (Fan Mod)
Operating System
Windows 10 Professional
Monitor
2x Samsung SyncMaster E2420 23.6" 1920x1080
Keyboard
Logitech G15
Mouse
Logitech MX310
▲ hide details ▲
NameUnknown is offline  
Sponsored Links
Advertisement
 
post #4 of 4 (permalink) Old 03-21-2019, 06:11 PM
Some call me... Bifford
 
BFRD's Avatar
 
Join Date: Dec 2004
Location: Carrollton, TX
Posts: 5,264
Quote: Originally Posted by NameUnknown View Post
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.



BFRD is offline  
Reply

Quick Reply
Message:
Options

Register Now

In order to be able to post messages on the Overclock.net - An Overclocking Community forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.
User Name:
If you do not want to register, fill this field only and the name will be used as user name for your post.
Password
Please enter a password for your user account. Note that passwords are case-sensitive.
Password:
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:

Log-in



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 


Forum Jump: 

Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off