I was wondering if anyone had any opinions on how to design a system that will link people (users/assignees), hardware, and software. Eventually, I will want to look at several tables (views?) of:
People and their assigned hardware+software
Each hardware unit and its assigned software and assigned person
Each software type and its (aggregate) users
I’ve already got a table just for people, but was wondering if I should:
a) put everything in a giant table
b) have a table for people and a table for “things” and link them both internally (software<–>hardware) as well as externally (hardware/software<–>people) through lookup columns and formulas
c) have 3x tables for each, and just link them that way
Each of these tables/categories, as I’m sure you’ve guessed by now, will have various columns in each of the with details (e.g. physical location of people, people’s job titles, model of hardware, status of hardware [ordered, broken, etc.], etc.).
Are there any advantages/disadvantages to any of these layouts? I’m not an info architect by any stretch of the imagination, so wanted to get the opinion of those who know this stuff better than me before I started on the database.
I think you are going to get as many options as responses…
I would start with one main table, with the most important columns of Person, Hardware and Software. These columns would be lookups into a Person, Hardware and Software select list/lookup tables.
On each of these lookup tables you would then have fields with the details needed for each dimension. (Person, hw, sw). Pull into the main table the fields from each of the tables that needs to be in any integrated reports.
In the old days, and with relational databases, people would have gone on about 3rd normal form. In modern data structures, including Coda, that is no longer a consideration.
More of a concern is the volume of data, and how to manage that once it gets too big.
Funnily enough the need to track hardware is something that I recently ran into.
I went through the same thinking that you are.
As with any project you need to define your scope and what you want to accomplish with the data. Planning in even if it won’t be implemented initially is far better than trying to alter course part way through.
I agree with Pete that you should be looking at separate tables.
Staff
Hardware
Software
and a table or two for allocation of staff to hardware/software
You could keep it as simple as that but then you go down routes such as “Do I want to know the history of a device?” Not just who it is assigned to/locate now but where it is had been and who with. If that is the case you need to store more info an track when changes happen. E.g. A computer that was originally Steve’s. He leaves and it goes into storage. Then it is reassigned. Do you need to be able to follow that routing over time.
If you want to track maintenance such OS reinstalls, hardware replacement/upgrade. Then more tables needed.
Finally with software is it just “who has what” or do you need to track licensing, renewal dates, costing. Would you track this over time too to see how much you are spending on each?
Honestly becomes a minefield.
I myself have a tendency to overcomplicate even a simple task
As I said it all depends on what you are trying to achieve. The simplest table structure above should get you started.
Thanks @Piet_Strydom and @Dale_Cowling ! I think I get it - some simple tables for the primary information and their (uniquely) associated data. For example:
Table 1 = People (names, positions, locations, etc.)
Table 2 = Hardware (names, serial #'s, model #'s, etc.)
Table 3 = Software (name, license #'s, version #'s, etc.)
And then when I need to associate/link any of these things, use a separate table via lookups. For example, John Smith was issued a Macbook with Adobe Suite would be:
Table 1+Table 2+Table 3 (all lookups)
If I ever wanted to extend the functionality of any of the tables, I could add uniquely associated rows to the original tables (e.g. date of software purchase) and pull them to any other table as a related column.
Does that sound right?
I’m sure there are different ways of doing things, but if I’m understanding that structure correctly, that sounds like a good way to do things. I’m often expanding/iterating my tables, but that sounds like a good way to set a foundation/process.
So I would label Main Table as table 0 - Because that is where I start.
If you want to track the relationships over time, use a date field. If you want to know what changed over time, have a date for every component change you want to track. You can even do that for hardware/ people changes.
Thank you! This may be me being daft, but based on the direction of your arrows, are you saying that the “Main Table” is where fields come “from”? It’s my understanding that lookup columns are only allowed on the “display” column. If that’s the case, wouldn’t the direction of the arrows be reversed (or again, does it even matter and I’m overthinking it)?
edit: yeah, I think that was, indeed, me being daft. I’m guessing you mean something like “that’s where the information comes from”. That’s exactly the way I set up my doc. Thank you again!!
