Asset Inventory

[joeyrego]

Hi Guys,



I have a question that I hope someone can give me some guidance on. I have assets that I need to keep track of. I have looked at the assets inventory template already but I am not sure if I can adapt it or if i'll need to start from scratch.

So I have assets that have the following fields below. I'd love to be able to use the Desktop Assets Inventory template for this. Can anyone advise me on the best way to get these one to many relationships set up? Maybe even a mock screenshot of what the tables and relationships would look like? Thanks for your help.



Domain - One server could have many
ServerName - Unique
IP Address - One servername could have many IP Addresses
Backup Network IP
Internal DNS - One servername could have many
External DNS - One servername could have many
External IP - One servername could have many
ISCSI IQN
ISCSI IP
ISCSI EMC IP
ApplicationsInstalled - One servername could have many apps installed.
Model
Manufacturer
Serial Number
Contract
Status
Location

MOD EDIT: Basically a repeat/continuation of existing thread https://www.accessforums.net/databas...ase-43125.html

[ItsMe]

I may use someone else's template to get an idea of how to do something but never depend on them as an "off the shelf solution".

[enquiries]

I agree, use it as a guideline but not the solution. Anyways, it'll help if you "group" things together and then the tables will come naturally. Can you give an example of the content/data.

[rpeare]

Starting from the top down

1. A table for your servers with a PK (do not use the server name as a PK use an autonumber field), this table would store things specifically related to the server nothing with a 'many' side of a one to many relationship.
From here you have several one to many relationships
Servers (1) - Domains (many)
Servers (1) - IP Addresses (many)
Servers (1) -Internal DNS (many)
Servers (1) -External DNS (many)
Servers (1) -External IP (many)
Servers (1) -Applications (many

What's not clear if any of these items are related, for instance for each domain on each server do you have one IP address, or multiple IP address.

What it comes down to is that you have to examine every possible one to many relationship in your data if there is a 'many' side you want to create a table for that set of data

For instance, in this example I would expect your domains and applications to be independent of one another so you would have a table for domains and a table for applications

Then if you wanted to track which domain had access to which applications you'd have a junction table listing the domain FK, the application FK (foreign key) and a PK. This is what's known as a junction table, relating two tables that are otherwise unrelated.