Upgrading from 5.5 to 7: relationships, tables and files

[bluearrow]

This is my first post. I hope I am sending it to the right forum. Please excuse me if not and direct me to the correct one.

I am upgrading from 5.5 to 8 and I am a little bit lost (I have no experience with version 7 or 8). My 5.5 database consist of 45 files, and quite a few relationships, fields and scripts. I have decided to rewrite it from scratch to version 8, given the complexity of my current database.

I use a Windows version, windows domain, 30+ network users.

Main database files are:

Companies (the headquarter or heading company)

Clients

Contacts

Address

Phone (includes email phone, fax, etc.)

Tender

Client line items

Project

Invoice

Freelancers

Freelancers line items

One contact may belong to several clients; have many addresses, many phones, etc.

One client may belong to only one company, but may have many addresses, many phones, etc.

One tender > one project > one client > one contact

One project > many client line items

One project > many freelancers line items

One project > many invoices

My first doubts are:

1. - I usually use a join file between contact > phones, clients > contacts, etc., to avoid data duplication and facilitate assignment. However, I am not sure I should do this is version 8. Should I? Are there any better alternatives? If you give me a link to a sample file, it would be great.

2. - I have always had a problem with client line items and freelancers line items. They both belong to a project, but besides the projectID, they have no other relation at all. This creates a problem for financial reporting, among other issues. I would like to merge these 2 files into one table in version 8. Does this sound logical?

3. - Should I use a join table between projects and invoices?

4. - The client line items are the same from tender to project to invoice. I just add the corresponding ID (tenderID, then in another field projectID, invoiceID, etc.). Is this the reasonable approach, or is it better to have different line items, even different tables?

I am scare of running into problems after merging, because of a wrong initial approach to the issue.

I would appreciate your advice,

Thanks

[CyborgSam]

bluearrow->

Welcome to the Forums!

8 uses the same relational theories used in 6, what differs is the implementation of tables & relationships. If your data model has not changed, then the physical tables in 8 can be the same.

For example, if a many-to-many relationship (like projects & invoices) required a join table/file in 6, one will be used in 8.

I suggest sitting down and carefully drawing an ERD (Entity-Relationship Diagram) so you fully understand how your data is organized and what should be improved when you update.

Good thing to remember: there's more than one right way to do a job.

[bluearrow]

Thank you CyborgSam,

Your reply clears all my doubts regarding the use of join files (questions 1 and 3). I am now in the process of designing the ERD, and this issue stopped me for a while.

How about questions 2 and 4? Maybe I have not provided enough info? I really would like to have a point of view that is different from mine regarding these to topics. As you said, there are more than one way to do things.

Regards

bluearrow->

Welcome to the Forums!

8 uses the same relational theories used in 6, what differs is the implementation of tables & relationships. If your data model has not changed, then the physical tables in 8 can be the same.

For example, if a many-to-many relationship (like projects & invoices) required a join table/file in 6, one will be used in 8.

I suggest sitting down and carefully drawing an ERD (Entity-Relationship Diagram) so you fully understand how your data is organized and what should be improved when you update.

Good thing to remember: there's more than one right way to do a job.

[CyborgSam]

2. - I have always had a problem with client line items and freelancers line items. They both belong to a project, but besides the projectID, they have no other relation at all. This creates a problem for financial reporting, among other issues. I would like to merge these 2 files into one table in version 8. Does this sound logical?

What problem for financial reporting does it create? Do you need another relationship to solve this problem? Assuming not, the two tables could be combined and a field used that has Client or Freelancer in it to differentiate between entries. This tactic was more important in 6 because it saved a file. In 7, it may be easier to leave them separate just so the Relationship Graph is easier to understand.

4. - The client line items are the same from tender to project to invoice. I just add the corresponding ID (tenderID, then in another field projectID, invoiceID, etc.). Is this the reasonable approach, or is it better to have different line items, even different tables?

If a line item is a unique entity, then it should not be duplicated in the database to make it relate to other entities. It sounds like your line item table already has the foreign keys to relate to the other entities.

A good concept to remember is to design the ERD as a model of how the physical process works without thinking about FileMaker. Once all the entities and relationships in the ERD are clearly defined, it should be easy to implement the ERD as fields, tables, and relationships in FileMaker.

[bluearrow]

Thanks again CyborgSam.

What problem for financial reporting does it create?

The client item lines and the freelancers item lines are located in different files. However, they might share a common projectID, invoiceID and other IDs (clientID, etc.). When reporting a project cost, the MK department ask me to show in the same report both line items, and to calculate many summaries, average, totals, etc. And the worst, subparts sorted and grouped with common elements from both line items files. For instance, subparts sorted and grouped from fields A and B from client line items and freelancers items (fields A and B are present in both line items).

Because of these, I have to create an endless number of concatenated fields, copy fields from one file to the other, and ellaborate calculations on not indexed fields. This make the report complex, and very, very slow to calculate and show (we are talking about hundred of lines, sometimes).

The initial reason to keep them in separated files was to avoid mising data while reporting (take into account that I need reports to include only client line items, reports for only freelancer line items, and reports that include both).

What is wrong with my approach (I guess something must be wrong? what would be a better design?)

Please let me know if you need further info.

4. - The client line items are the same from tender to project to invoice. I just add the corresponding ID (tenderID, then in another field projectID, invoiceID, etc.). Is this the reasonable approach, or is it better to have different line items, even different tables?

If a line item is a unique entity, then it should not be duplicated in the database to make it relate to other entities. It sounds like your line item table already has the foreign keys to relate to the other entities.

Yes, it does. The problems are:

1.- Not all the Tender line items move to project. Some Tender are not accepted, some are error lines, some are stopped, etc. This lines would have the projectID and invoiceID empty.

2.- Not all the Project line items go to an invoice (therefore, some line items would have the invoiceID empty).

How do you "close" those line in a standard situation? In my design, they remain in the file (tender or project), but I need to flag them for reporting among other reasons. This makes them "difficult" to control and manage. Maybe this is normal. I do not know.

A good concept to remember is to design the ERD as a model of how the physical process works without thinking about FileMaker. Once all the entities and relationships in the ERD are clearly defined, it should be easy to implement the ERD as fields, tables, and relationships in FileMaker.

I fully agree, but even after having an ERD, the users might have forgotten to tell you something, and then...

In my case, after having developed the application, I had to create new files and move records out of one existant file into a new one, to accomodate to the real workflow.

[CyborgSam]

1.- Not all the Tender line items move to project. Some Tender are not accepted, some are error lines, some are stopped, etc. This lines would have the projectID and invoiceID empty.

2.- Not all the Project line items go to an invoice (therefore, some line items would have the invoiceID empty).

How do you "close" those line in a standard situation? In my design, they remain in the file (tender or project), but I need to flag them for reporting among other reasons. This makes them "difficult" to control and manage. Maybe this is normal. I do not know.

Create calculations that take advantage of an empty ID field to determine the line item's status. For example: ItemNotInProject = IsEmpty ( projectID ). Another: ItemNotInvoiced = IsEmpty ( invoiceID). For these calcs, be sure to uncheck the Do not evaluate if all referenced fields are empty. These calcs can be used in create relationships to filter the line items.

Another tip: FileMaker treats 0 as false and any other value as true. So a relationship can also use a test for <> 0 instead of an extra calculation. One end of the relationship will need a 0 or 1 for comparison, create a calculation which simply contains a 0 or a 1 (don't make it global or it will match any record).