I have an issue. Database design is not at all my forte' but I am being fasked by my boss to learn how, asap. My boss owns 15 small business's, everything from a small weekly Newspaper to an Electrical Service to a Marina. We really dont have any real database system in place for any of the business's except some simple tables to keep track of simple things. He is a filing cabinet guy. He came to me yesterday and asked to create a contact, or more specific a "mailing list" database for all his business's. He wants all contacts in the same place (a table) but to be able to extract and search for them specific to the business (or business's) they relate to. Ok, no problem. A "business's table", with Business_Name, Bus_ID, Bus_Address_Street, etc, etc. Then a "customer, or contact table", Cust_ID, Cust_Name_Fisrt, Cust_Address_City, Business_ID (for relating the tables), etc.

Ok, that was great. I made two tables with all the fields I could think of, connected them using the Business ID, they worked, I entered the companies data and some customers with the ID for a business for each cust, and it all seemed to work well---I did a few searches and it gave me the data I was looking for. But! That's when it dawned on me, what if a customer is a customer for more than one business? The way I had it set-up, the Customer/Contact table only had 1 field for Business_ID. That's seems like a huge brainfart flaw in my simple two table design.

How do I set this up? Is it as easy as simply adding a field for each business on the customer table (something like Bus_ID, Bus_ID_2, Bus_ID_3, etc)? It doesnt seem like that would work out.

1 company has Many Business's. Many Business's have Many Customers. I'm pretty sure that would be correct for an ER, right?

do I need a seperate database, or table(s?) for each business?

Ahh!:eek: I have so much respect for you database designers. This is a great exercise for my newbie brain! Any advise?

FYI, I will be using FileMaker Pro 8 as my software (my entire network is Apple), not that it should matter.

From my experience you could setup a third table and just have the primary key for each contact and each business in there. So this way multiple businesses could be associated with contacts. Of course this makes your querys a little more complicated. I would search for many to many and Filemaker pro and see what you find.

Good luck,

Fred