AnsweredAssumed Answered

Self-Join Relationships or Multiple Tables with Join Tables

Question asked by codycuellar on Sep 1, 2016
Latest reply on Sep 1, 2016 by Fabrice Nordmann

Hello everyone, I'm new to this forum, and also quite new to Filemaker, so please forgive my rather limited knowledge of the program and methodologies. I am trying to update and build new databases for our company and had a few questions on how to get started in terms of choosing how to set up initial tables in the database.


I already put together a contacts database which contains a table for people, a table for business, and a join table that pulls FK from the other two and creates relationships of which people work for which company and adds a field for their position. That way I put a portal on the entries for people to display the multiple companies they work for (common in our industry) and a portal on the companies layout to show the multiple people that work for said company.


Now I'm trying to update our inventory and am not sure which direction to go with the tables and relationships. Here's the scenario:


We have a lot of fixed assets that get purchased and used until broken or no longer needed and/or sold (mostly computer hardware). We have many computers that generally don't change too often but I would like to track the hardware in each, as well as software. This brings us to the software which has a range of licensing types from server distributed, to individual serial keys per license. I want to be able to track which computer currently has which licenses installed so they are easy to manage, as well as to know how many available licenses we have on a particular piece of software. We then have a small portion of sell-able inventory that gets purchased and sold as part of our products (most of our products are services and digital files which are tracked in an entirely different system). The final semi-related list is a way to track passwords for machines, web-service logins, FTP users, etc.


So, that being said I would like to be able to track a computer, and on the record layout, be able to see which software is installed, which licenses are being used, what hardware is installed, and what passwords are associated with this computer (admin accounts, VNC, etc). I also want a way to track the sell-able inventory which is used by a specific department of the company, where we can also track resellers and keep up on current prices (possibly pulling info from our contacts DB).


The obvious first problem is permissions and access control. There's only a few specific people in our facility who can have access to the passwords portion, but there may be several people in IT or other departments who need access to the computers/fixed assets. Then the inventory bit is only needed by a specific department. So my initial idea was this:


- Table for Fixed Assets

- Table for Computer Systems

- Table for Passwords

- Table for Software

- Table for Inventory

- Join Table between Computers and Fixed Assets

- Join Table between Computers and Passwords

- Join Table between Computers and Software


I'm not sure if this would quickly get difficult to manage or if I would be losing the ability to do certain things like reports that I may need, but the other option was to have a single Assets table and make computers, software and fixed assets all as entries in the table and categorize them with a "type" field. Then use self-joining relationships and portals to pull the hardware into the computers, etc. Is this a better way to set it up? What if I want to go to a layout that ONLY displays the computers, can I filter the results of table to a specific layout? Also, when using the portals, when I have a drop-down menu to add a piece of hardware to a computer, won't ALL the assets in the whole table show up or can I filter those portal fields to only display entries from the "computers" category?


Either way I think sell-able inventory should be its own table since it doesn't really tie into the others at all. Passwords I think should be its own database and in order to restrict permissions may have to pull the computers in to that DB, but not the other way around. We have FM13 server and can set up permissions with the active directory.


Sorry that this is so long, I have just been researching for a while now and really can't figure out what will be easier to manage and also be future-proof. Any guidance would be greatly appreciated, thanks!