"Complicated" Database Design

[123]

Hey,

I started working with FileMaker a while ago and so far it's been pretty fun and fairly easy to learn because I worked with MySQL in the past. However, there are still a few questions I have. I'm used to having complete freedom when it comes to databases, because in Python or C++ for instance I would simply write my own queries and get whatever data I need, no matter if a database is related to another or not. Anyway, the flexibility is still what causes me some problems. I have the following problem.

I'm working on an ERP (Enterprise Resource Planning) software for the owner of several restaurants. I need to have the following tables to start with:

• Restaurants
• Employees
• Jobs
• Visa
• Health Insurances
• Meetings

Now, the person using the solution needs to be able to create as many restaurants, employees, jobs and meetings as they want and assign them to each other. For instance, Restaurant A is going to have 5 employees, Employee A, B, C, D, E and so on, and all these employees will have one or multiple (this is really important) jobs assigned to them. This process has to be flexible, so I can assign an employee to whichever restaurant and job I choose. You also need to be able to assign one or more visa to a job (and the assigned employee), the same goes for one or more health insurances. The next problem is about meetings. Each restaurant needs to be able to have meetings every once in while, which need the ability to keep track of employees that attend the meeting. So you should be able to select employees form a portal or card window (in FM16 for instance) and they will be added to another table MeetingAttendees or something similar. Again, this needs to be flexible so every restaurant can create meetings and check which employee is present. (I need to be able to create a layout to display the meetings and who has been present, if possible also to see who wasn't).

Normally I would simply take the IDs and create tables that join these iDs together, however I'm not able to recreate this in FileMaker, if one relationship works, the other breaks for some reason. If anyone could give me a simple layout of what my database needs to look like, or if possible and not too much to ask an example in FileMaker. Your help is much appreciated.

Thanks in advance,

Mike

[Steve Martino]

 

2 hours ago, 123 said:

Normally I would simply take the IDs and create tables that join these iDs together, however I'm not able to recreate this in FileMaker, if one relationship works, the other breaks for some reason

I think you may be confusing Tables with Table Occurrences.  You  can have multiple table occurrences to create the relationships you need to accomplish your task.  

I don't have a complete idea of how I would set up the DB, but some thoughts.

Restaurants can be a table.

Employees a table.

But I may put an Assignments Join table between them, so while an employee is at one restaurant, you can see his assignments.  I also have to assume assignments should have a start and end date. For reporting you can then pull up an employee and see his assignment history, and where he is currently assigned.

For meetings, the relationships get a little more interesting.  Do you need to only pick employees who are currently assigned to a restaurant when the meeting occurs, or any employee in the employee pool can attend any meeting at any restaurant at any time.  Meetings would be their own table where your assign the restaurant to the meeting (date time etc.), and employees based on the above mentioned criteria.  And here is where you could use another table occurrence for a value list or picker list that will only show the employees assigned, once the restaurant is chosen.

As you are aware, it's probably most important to completely (or what you think is completely) nail down the relationships on paper before you start building to avoid the scenario of going too far in the wrong direction and wasting time starting over.

[ggt667]

FileMaker lingo = unix / SQL lingo

---

table = table

table occurance = table as seen in a specific query

layout = view AND / OR CUBE

valuelist = sort | uniq as in ORDER BY

relationship = part of query AND / OR CUBE

7 hours ago, 123 said:

Now, the person using the solution needs to be able to create as many restaurants, employees, jobs and meetings as they want and assign them to each other. For instance, Restaurant A is going to have 5 employees, Employee A, B, C, D, E and so on, and all these employees will have one or multiple (this is really important) jobs assigned to them. This process has to be flexible, so I can assign an employee to whichever restaurant and job I choose. You also need to be able to assign one or more visa to a job (and the assigned employee), the same goes for one or more health insurances. The next problem is about meetings. Each restaurant needs to be able to have meetings every once in while, which need the ability to keep track of employees that attend the meeting. So you should be able to select employees form a portal or card window (in FM16 for instance) and they will be added to another table MeetingAttendees or something similar. Again, this needs to be flexible so every restaurant can create meetings and check which employee is present. (I need to be able to create a layout to display the meetings and who has been present, if possible also to see who wasn't).

Normally I would simply take the IDs and create tables that join these iDs together, however I'm not able to recreate this in FileMaker, if one relationship works, the other breaks for some reason. If anyone could give me a simple layout of what my database needs to look like, or if possible and not too much to ask an example in FileMaker. Your help is much appreciated

You will add a record through a relationship aka related record, in Filemaker there is no need to fiddle with the keys and related keys, you just need to add a record in a related table through a relationship; possibly via a portal.