1 2 Previous Next 15 Replies Latest reply on Jun 2, 2017 10:49 AM by fmpdude

    Question about Data Model Design


      Hi All, quite new to FileMaker and building a system to help us manage our lighting design projects.  I’ve been experimenting with some different techniques to get the results I want and I’m getting things to work and having some fun but I’m struggling with the overall data model and how it relates to the relationships I build.


      We design lighting schemes for people (and they are usually individuals) though sometimes they are companies.  When we start the outline design to start building up the specification and cost they might not be our clients, we still might be selling to them.


      Our clients may have more than one project with us.  Those projects might involve more than one building but they’re usually on the same site. Projects have their own attributes.


      At its heart, a lighting design is a detailed list of lighting circuits and what light fittings are on each circuit.  It’s important to know which room / floor / building each circuit is in.  A circuit has its own attributes - where it goes from and to, how it is dimmed, what it is lighting.  A light fitting has a lot of attributes - colour temperature, power, beam width etc - and the number of products we use is large, from a number of manufacturers.


      We produce designs in different stages - proposal, draft, v1, v2 etc and from those designs we ultimately issue technical specifications for the electrical teams,  descriptive schedules for the clients, purchase orders for our suppliers and invoices for our clients.


      I’m comfortable (I think) with the Client entities, the Product, Manufacturer and Supplier entities.  I can’t get my head round how to deal with the building blocks of the technical designs themselves.   I seem to be trying to create too many entities.  It might be that my model is more complex than the ones I see in examples but it can’t be that odd.


      I think I need the following tables to manage the projects themselves




      A join table for Products & Circuits - we could call that Specifications or Designs and it could have additional attributes - status, stage etc



      I don’t know how to deal with Rooms, Floors, Buildings - do I create a separate entity for each or are they better treated as attributes of another entity.  I was thinking separate entities and then using TOs to create a simple data entry layout where a designer could “build” the structure of the building in one layout with multiple related portals


      Sorry for the long post but I’m hoping someone here can help.


      Using FM Pro Advanced 15 for Windows


        • 1. Re: Question about Data Model Design

          I would recommend getting a tool like the inexpensive "SQL Editor" so you can diagram your databases, and thus really understand it yourself, first.


          Design first, code second.


          This tool is much more sophisticated, but not difficult, than the Relationship graph and it will even generate your FM database once you've designed your ERD. Using a tool like this also gives you a way to print a nice ERD to present to the client for discussion. SQL Editor will also import your existing FM database tables into its graph!


          Using the ERD, you can make sure that your queries work by making sure the required join keys are present in tables, for example.


          And finally, SQL Editor will work with just about ANY database, not just FMP.

          ERD software is an essential part of creating a database, but it's really not covered in depth on the forum.


          Plus, SQL Editor a fun tool to use.


          SQLEditor for Mac OS X




          Lynda.com also has courses on data modeling using FileMaker!



          • 2. Re: Question about Data Model Design

            I had an electrical contractor client with similar needs.


            I had Companies and People, two separate tables. With a join table "companies_people" as needed, because people sometimes needed to relate to Companies - even many to many. These can be clients, vendors, suppliers, etc. Sometimes a person, sometimes a company.


            For other tables where additional data was needed (addresses, phones, etc.) I had two foreign keys companyID_fk, personID_fk. If the Company was the primary then that key was completed and any related People would be "viewable". If a person was a primary then that key field was completed (and if they had related Companies, those could be linked).


            There was never a record where both foreign keys were populated, but I suppose it is possible. You just handle however you need.


            It sounds like you might need a "location" table with granularity and the multiple foreign keys. Location isn't necessarily an address. And there can be links to each other. This is also what I did (gps, farm, field, gate, etc.)


            OTOH, perhaps study EAV (entity-attribute-value) storage (here and elsewhere). This allows you to add attributes without a separate table for each kind of attribute. It's a different way of thinking (NON SQL), but may help you.


            Excellent suggestions by @fmpdude. You can use paper and pencil, too.


            You might also look at web solutions for this (MySQL/PHP) that you could try and get an idea of the structure. Then turn into FMP.


            • 3. Re: Question about Data Model Design

              Thank you and yes I'm designing first, coding second and it's the location question that has me stumped because I can see a couple of ways to go and I'm not sure which to follow.  Do you have a link for SQL Editor please.  There seems to be a number and I'm not sure which you are referring to.

              • 4. Re: Question about Data Model Design

                Thanks beverly.   A Location table with multiple foreign keys sounds like where I am headed I think. Would those foreign keys be for each of the different location entities you mentioned in your example (gps, farm, field, gate, etc)?


                I've mapped it out on paper and I can see both ways might work.  Where I am hesitant is that most of the locations in my example really only exist for presentation purposes.  I'm not really interested in the building, the floor or the room.  I just need to know that the circuit (which is an entity that I'm interested in) is in a particular building, on a particular floor, in a particular room.  Which makes them feel more like attributes of the circuit.  Sorry, I don't know how to explain my problem better.


                • 5. Re: Question about Data Model Design

                  Yes they are attributes. The flexibility of EAV would work here.


                  Sent from miPhone

                  • 6. Re: Question about Data Model Design

                    I included the link in my posting above.

                    • 7. Re: Question about Data Model Design

                      you did, sorry.  It was so obvious I missed it.

                      • 8. Re: Question about Data Model Design

                        No worries. The link gets converted into a readable text link instead of an obvious URL.


                        Let me know if you have any questions on SQL Editor.

                        • 9. Re: Question about Data Model Design

                          More details (not on phone now...):

                          Locations (table)

                          Entity (number field,  foreign key id that matches the circut primary key)

                          Attribute (text field)

                          Value (text field)


                          I might have a value list of static phrases (or from this Attribute field, so it is dynamic). That way the selection is consistent (Building not building or bldg.). Therefore you can find by the attribute a particular Building (or all)!


                          Because the Entity is related to the Circuit, these can be in a portal (for view, entry) and use various means to display/report.


                          Trying to make a table for every Attribute would be a nightmare. Trying to make a field for every Attribute would also be a nightmare. This is flexible as not all Entities would have all attributes, but that's ok!



                          p.s. Yes, you can have other fields in your Locations table if you think these are helpful...

                          • 10. Re: Question about Data Model Design

                            Thank you.  I'm going to experiment with that and see how it works for me.  It's a very different way of structuring things and I need to get my head around it.

                            • 11. Re: Question about Data Model Design

                              For me, "design first, code second, only goes so far. I find that it's a cycle as I work with the implementation of my design and present them to the users, new ideas, needs, limitations are discovered and I update my design.


                              Thus, it becomes design, code, test, rinse and repeat.

                              • 12. Re: Question about Data Model Design

                                Absolutely! And do you then plan to have rooms inside buildings (related)? Quite a bit to consider if needed. A location may be hierarchical as well. Mostly think about how you

                                1. enter data

                                2. report on that data

                                Then what makes the most sense?


                                A simple field may be sufficient:


                                Campus: 3

                                Building: Greenroof Studio

                                Room: 5

                                Directions: North corner, 3rd floor, left wall as you walk into the room



                                Of course connecting these is not "normalized" data, but do you really need that granularity? Or perhaps some of it is normalized (with EAV or otherwise) and some of it is "free-form text".


                                You may start with one method and decide you need something else.


                                • 13. Re: Question about Data Model Design

                                  Sure, depending on how many (if you have any) requirements, sometimes bottom-up is the way to go. Or even middle-out. Top down is my preference when possible, but it's not always .. possible.

                                  • 14. Re: Question about Data Model Design

                                    Except that I wasn't describing "bottom up". I'm just pointing out that as you start out, you usually cannot anticipate everything and will need to revisit your basic design periodically. Thus, one should not stress out over the possibility that you haven't allowed for every little detail in your original ERD. FileMaker is pretty nice in that you can easily reinvent the solution as you go when a user suddenly clarifies or adds a requirement or the current design sparks ideas for an even better design as you implement your initial plan or as you demonstrate it to the users.

                                    1 2 Previous Next