Member Avatar for dmw

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.

  • 3 Contributors
  • 3 Replies
  • 196 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by markcaldwell
Member Avatar for fbnewtz

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.

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

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

Member Avatar for markcaldwell

the problem is as big as the tools you have to work with. the better the tools the smaller the problem. So, let's start at the beginning...
1. what are you using for a database engine? MS Access (2003?,2007?) , MySQL, any other?

2. have you ever worked with a database before?

3. Access 2007 has several easy to use "Templates" that can give you a push in the right direction. Most of them are fairly easy to manipulate for a final product.

4. There are also alot of decent "onLine Tutorials" that offer a wealth of information.

If your boss owns 15 business than the expense of Microsoft Office Professional with Access would be a small price to pay.

Edited by markcaldwell because: Additional info and spelling correction
Member Avatar for markcaldwell

the problem is as big as the tools you have to work with. the better the tools the smaller the problem. So, let's start at the beginning...
1. what are you using for a database engine? MS Access (2003?,2007?) , MySQL, any other?

2. have you ever worked with a database before?

3. Access 2007 has several easy to use "Templates" that can give you a push in the right direction. Most of them are fairly easy to manipulate for a final product.

4. There are also alot of decent "onLine Tutorials" that offer a wealth of information.

If your boss owns 15 business than the expense of Microsoft Office Professional with Access would be a small price to pay.

SORRY!.. I should have been for careful while reading your post. You stated that you are using Filemaker Pro. I have been designing databases for over 20 years ... But I have no knowledge of Filemaker Pro. please forgive my ignorance.:$

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.