6 Replies Latest reply on Aug 31, 2015 2:12 PM by jmyers.svt

    database design question


      So I have an existing FM database currently being used for our work order management. A quick background. We are a locksmith company that does mainly commercial.


      With that being said, we have master key systems for each building that we currently track using paper. I am looking to get away from that using filemaker.  I can export the master key system database into an excel doc and import into a table and display it how I need. Thats not the issue.


      My question would be, how would be the best case scenario of storing the information be? Should I create a new table for every single master key system? Or would there be a decent way of putting them all in the same table and somehow assigning them with a matching customer/building?


      I feel like if they were in the same table, it would get a bit excessive. And I would still have to make a layout for each system tied to each customer so that it would only show the related records. There is the possibility of having 10,000+ records on one system. The typical is anywhere from 2k-5k though. Just know, the potential is there.


      Im open to any and all suggestions.



        • 1. Re: database design question

          Well, in order to answer the question properly, we'd need to know what a "master key system" really consists of. However, on first blush, when someone asks, "Should I have a different table for each X," the answer is generally no. You wind up creating a new table every time you have a new X to track, so it's a really bad idea.


          As for "is there a decent way of putting them all in the same table," sure. You have an internal ID (usually using UUIDs), and then you can have any number of fields for differentiating them from others. As for assigning them to a matching customer or building, you would make that decision based on whether the relationship between them was one-to-many (one key system to many customers / buildings, or many key systems to one customer / building), or many-to-many (many key systems to many customers / buildings). If it's the former, you just tie the records together using a primary and foreign key. If it's the latter, you would normally build a join table, where each record represents the unique combination of the two parent tables (like key system and customer / building).


          10,000 records is trivial; that's what Find is for.   :-)   You can also use portals and filtering to present just the data you're interested in.


          I think it would probably benefit you to grab a copy of the FileMaker Training Series and go through the Data Modeling chapter carefully and slowly. Good data modeling practices are crucial for every developer.





          • 2. Re: database design question

            Thanks for your input.


            I understand data modeling so thats not a big deal. I do not mind setting up a table every time a new system gets put in place because it only happens when a contract comes into play and it will stay in the system. I was just hoping one was more efficient than the other based on the fact that we will constantly be searching within each system. And if there are multiple systems setup in one table, its possible to have the same descriptions..


            A typical master key system looks like this...



            KEY ID           CHANGE KEY        BOTTOM PINS            MASTER PINS          DESCRIPTION

            AAA1              123456                    123456                        123456                     suite 123






            Now imagine if that was tied to a customer using a one-to-many relationship. Customer having many of those related records but they will all be unique to the KEY ID.


            However, if I have another system in the same table tied to a different customer, their keys will possibly have the same KEY ID. Filtering using a portal is an option, but my techs need the ability to very easily search for things in the description or KEY ID fields.


            Thats the reason I ask whether or not using separate tables would be beneficial over putting everything in one table.

            • 3. Re: database design question

              From what you have written, it seems that at least there could be 2 tables.  One for the customers/buildings and the other for the keys. Each customer/building record would have its unique id (UUID) along with a description of the customer/building and all records for keys for that customer would have a foreign key containing the key for that customer.  You could set up a portal to show all keys based on which customer you are working with.  I'm sure it may be more complicated than this, but this would be a simple way to organize your keys / customers.  Mike's suggestion of reading the Filemaker Training Series is a great one.  Module 3 (Chapter 3) is all about data modeling.

              Cheers, Mark

              • 4. Re: database design question

                jmyers.svt wrote:

                I understand data modeling so thats not a big deal. I do not mind setting up a table every time a new system gets put in place because it only happens when a contract comes into play and it will stay in the system.

                Even if you don't mind doing that, it doesn't justify bad design.

                You can save yourself and possibly someone later down the line, a lot of trouble by getting it right.

                I agree with Mike, the model should be split into their separate entities and where needed a join table should be used.


                I'm asking myself the question: what is the data model? what are the entities?


                There should be only one Key that is unique?

                > table: Keys

                The key belongs to a Building which has Suites where Customers live?

                > tables: Buildings, BuldingSuites, Customers,

                table: BuildingSuites

                     fields: id, id_building, suite description, nr etc...

                That takes care of configuring the Buildings and Suites where you give them the Description.


                I guess there can only be one unique Building Suite and only one Key will match to that place.

                But possibly many Customers living in a Suite may have copies of the same key.

                If the Keys are serialised you should match them with the BuildingSuite id and customer id in another Join Table.


                So the final table to work on will be ( simplified ):


                id, id_BuildingSuite, id_Key, id_Customer


                When all the relationships are built you can put related fields on the layout and perform Finds any way you like.

                If you search for a Customer, Suite Description or Building you will have the results.


                You can see what Customers are in a Building, what Suites they belong to and Keys they have.


                Does that make sense or is my thinking flawed?

                • 5. Re: database design question

                  Your KEY ID field ... isn't. It's not unique to the key, except within the particular key system. A key is a unique entity, and the ID of that entity must be (by definition) unique. Therefore, as I mentioned before, you would use a truly unique key (like a UUID) for the purpose of relational joins. If you want to allow your techs the ability to search by customer or key system, then that can easily be done by incorporating a pull-down on a global field that filters the available found set automatically (via Custom Menus).


                  The alternative becomes nightmarish when it comes time to do data migrations, reporting, building navigation, building searches across key sets, etc. It's not just building the extra tables; it's all the additional structure that's involved. Plus, think about this: Your tech walks in with nothing more than a KEY ID value. You really want him having to navigate to, say, 10 different layouts to figure out which one it really belongs to?


                  Look, you can do whatever you want; it's your system. But violating basic relational design principles because you "don't mind" building extra tables is an "easy road now, hard road later" approach. And the road later will be hard.

                  • 6. Re: database design question

                    You are correct. My KEY ID field is not considered the UUID. It is unique in each master system, but I would not be able to use that as the Uniquie ID for the table.


                    Thanks for the input everyone. If I have any more questions I will be sure to post it up.