Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Table relation


rliska
 Share

Recommended Posts

Hi. I've been using CMMS (computerized maintenance management software) for a few years and find them highly useful but generic. I'm in the process of developing a customized database for business use and have a few questions. I'll start with this one. I have tables and layouts, so far, for the following- Assets, Manufacturers, Suppliers, Contractors, Work Orders, Employees, etc. I'm looking for direction with a proper relationship between a physical Asset and the Manufacturer, Supplier and Contractor. In the Assets layout I have a link by those fields that needs to lead to the related Manufacturer and/or Supplier layout that is specific to that asset. Each Asset, Manufacturer, Supplier and Contractor has a unique identifier field.

 

I have no issues with the script to open a new window and show the appropriate table, just the specific, related record(s).

 

Thanks

Link to comment
Share on other sites

Manufacturers, Suppliers and Contractors all come under the heading of organisations and can be stored in the same table. You can distinguish between them by storing their type in a field. Now many assets can be related to many organisations. You need a join table for this. The general setup for this is

Organisations             OrganisationsAssetsJoin              Assets
OrganisationID   -----------< OrganisationID_fk
OrganisationType
                             AssetID_fk       >-------------  AssetID

 

Now you can view all organisations linked to an asset through a portal showing records from the join table. You can jump from assets to organisations using the Go To Related Records script step.

 

If you need more help, please post again.

Link to comment
Share on other sites

Maarten, thank you for the direction. My preference is based on a historical perspective with CMMS programs. The unique identifier for each of the "Organization Members" in the program I've used has been- MAN-001, for Manufacturer, SUP-001, for Supplier, CON-001 Contractor, etc. This also applies to Assets and a separate table for each category; Equipment, Tools, Rooms, Buildings and Grounds. They would also have corresponding and unique identifiers as well. The same letter/number scheme would apply. Of course, Filemaker would handle the serialization of all and subsequent records.

 

Thanks

Link to comment
Share on other sites

Ah but there is a big difference between relationship keys based on a serial and asset serial numbers. The latter has no meaning, or should not have any meaning. The former does so. At least those of a puritan database heart will tell you so and I tend to follow them.

Apart from that, my personal rule of thumb is that you should not split data that is similar into separate tables.

 

Now while it is easy to link your asset serial to the primary key serial of a table and somewhat harder to create independent serials for your assets based on their type, I urge you to also take the pros and cons of separating similar data with respect to database structure, search routines, scripting, navigation and reporting in consideration. But, your mileage may vary.

Link to comment
Share on other sites

I think my explanation was not concise. The only identifiers used for relational purposes are those I direct Filemaker to produce. The actual asset serial numbers are not included for anything other than data for that asset. I also like to use a "Smart Numbering" system for bar coding assets, that in turn is the asset ID in the record. Works well for scanning an asset in a search field to pull up history, etc. Hence the alpha-numeric ID's. I try to avoid simple numeric ID for a variety of reasons. On the issue of asset similarity, for this purpose, there is no industry similarity between a door closer and a chain saw, or wireless network equipment and a clothes dryer, for instance. Yes, they are all parts of a whole, so to speak, but provide very different functions. Therefore, are tracked, grouped and maintained differently. I agree that simplicity is fundamental as long as the results meet the need. Part of this process is gnashing through want and how I get there. I truly appreciate your input.

 

Thanks

Link to comment
Share on other sites

The actual asset serial numbers are not included for anything other than data for that asset.
very good :)

 

On the issue of asset similarity, for this purpose, there is no industry similarity between a door closer and a chain saw, or wireless network equipment and a clothes dryer, for instance. Yes, they are all parts of a whole, so to speak, but provide very different functions.

 

Still, turning back to your original question, I'd say in many cases you need a join table structure to link many suppliers to many items, for instance. There will just be more of them if you keep stuff separate, for instance if you have suppliers that provide both chainsaws and door closers (I know they exist ;) ). One more thing, a manufacturer may also be a supplier, and so may a contractor (I know these exist, too :D ). If you're absolutely sure that this will never be the case for your database, then OK. If not, then you are duplicating data with all the risks thereof.

 

Therefore, are tracked, grouped and maintained differently.

I'd have to know more about your procedures and the exact nature of the database requirements to be able to say anything about that.

 

For instance, you mentioned grounds, buildings and rooms. Separate tables or not? You probably have floors in the buildings, elevator shafts, airco installations and so on. You may have a complex of buildings. A floor is not a room, and for maintenance purposes it is an entity with relevance (for instance, you might want to paint a whole floor in the same cycle in one go with one color scheme for all rooms on that floor). An elevator shaft is not a room also, but for the same reason (maintenance cycles, access privileges, keycard distribution and so on) it has similarities that from a database standpoint may be good to keep together.

I'm going to stop now because I do not have a clear picture of this and may only be spreading fog instead of helping.

Link to comment
Share on other sites

Yes, it gets interesting. So, If I have a drop down field that includes- Manufacturer, Supplier, Contractor, etc., can there then be an ID field that provides auto serial generation based on the chosen value in the drop down field? A more intelligent conditional value field, as it were. If so, this would allow me the ID scheme that I want and a single table for all assets.

 

As a side note, I've gone through the Filemaker Pro-The Missing Manual and either have not made the correlations to my needs yet or they are not specifically there. I would greatly benefit from sitting down with someone for a day. I absorb much better that way.

Link to comment
Share on other sites

So, If I have a drop down field that includes- Manufacturer, Supplier, Contractor, etc., can there then be an ID field that provides auto serial generation based on the chosen value in the drop down field?

 

I'd say yes. There are various ways, I'd write a script that does the work. Preventing duplicate values is the main issue to address. Do you need assistance with that?

 

As a side note, I've gone through the Filemaker Pro-The Missing Manual and either have not made the correlations to my needs yet or they are not specifically there. I would greatly benefit from sitting down with someone for a day. I absorb much better that way.

 

I'm all for sitting down for a day, but I live on another continent.... You could probably find a developer in your vicinity.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use