Two Companies within a single Database?

[orge]

Hi,

I'm working on a business application that will be used to run the day to day processes for two different, but integrated companies.

To the end user, we wish everything to be separate with the exception of the invoicing table (which should run consecutively according to the entries from both companies).

My initial thought was that this could be accomplished by filtering the tables according to a tag on each entry (i.e. every entry is tagged whether it "belongs" to company A or . This has the advantage that it would be easy to maintain entries for both companies (although this is not a requirement at this stage). The down side is that maintaining consistency requires querying/saving/retrieving sets whenever we switch between different layouts/companies/tables. Overall, I figure this will work, but I'm not sure it is best practice.

My alternative thought is to maintain two sets of relational databases and tie them together at the invoice stage with the method described above. This has the advantage that it should work faster and would not require as much complicated scripting between layouts - at the expense of making it difficult to keep duplicate entries.

I would be interested to hear if anybody has any opinions on these suggestions or alternatives. My filemaker experience is more theoretical than practical, so it's difficult for me to be sure I'm not barking up the wrong tree.

Many thanks,

James

Edited June 9, 2009 by Guest

[Vaughan]

Here is a thought: perhaps the invoice tables can be separate, but use a shared LineItems table, which is where the grunt of the invoice data is actually kept anyway.

That way people are restricted to seeing only the invoices that are visible through their company's invoices, but for reporting all the line items are together.

[orge]

Thanks for the suggestion, however there are a few of points:

1. The accounting requires consecutive invoice numbers. I can actually see how you could get round this using a custom auto-enter script to set the invoice number to the highest+1.

2. They do really want to be able to see invoices from both companies at the same time.

3. There aren't actually any lineitems. It's a service industry and the quotes consist of single item.

The more I think about this, the more I feel that it is not going to be possible to implement this with a relationship. To meet the requirements, at some point it is going to be necessary to relate one key back to two tables.

I've been working on option 1 and, so far, that seems to be going ok.

Thanks,

James

[comment]

I can actually see how you could get round this using a custom auto-enter script to set the invoice number to the highest+1.

I can actually see how this would fail when two users are creating an invoice at the same time.

There's not much detail here, but I would tend strongly towards keeping both sets of data in the same tables. Especially in version 10, where script triggers and saved finds make the interface aspect a lot easier.

[orge]

Yes, I tried to keep it brief, but I appreciate there's not much to go on...

I agree with you that it's probably safest to keep everything in the same table and manipulate the interface.

Thanks,

James

[LaRetta]

James, I too highly suggest you do NOT use InvoiceNumber + 1 for numbering your invoices. In multi-user mode, you will be guaranteed to get duplicate invoices. And when you do, both customers will get ALL LineItems from BOTH invoices and you will have egg on face (to say the least). :smirk: