AnsweredAssumed Answered

Question about Data Model Design

Question asked by iainshaw1 on May 31, 2017
Latest reply on Jun 2, 2017 by fmpdude

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

 

Products

Circuits

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

Projects

Clients

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

 

Outcomes