Best Design Question

[Seth Schrock]

I am creating a database that, among other things, tracks customers. Some customers are actually businesses that have partners, but there are very few of these. Currently I have it setup with a Customers table and a Partners table with a one-to-many relationship. Whenever I make any sales, they are related to the partner, not the customer directly, so that I know which partner made the purchase, but then totals are based on the customer through the partner.

The problem with this setup is that I have a lot of Customers whose name is the same as the only partner. For example,
Customer Name: Schrock Seth
Partner First Name: Seth
Partner Last Name: Schrock

So I'm duplicating the name many times just so that I can have

Customer Name: Schrock Farms
Partner 1 First Name: Seth
Partner 1 Last Name: Schrock
Partner 2 First Name: Joe
Partner 2 Last Name: Smith

So my data is perfectly normalized, but it is messing with the functionality for the user, especially since they have no concept of normalization and don't understand why.

Is there a better way of setting up the tables so that it is still normalized, but is easier to use? I still need to track sales for Schrock Farms (as in the above example).

[mshmyob]

If I am reading this correctly then you have a many to many relationship not a 1 to many. If Seth Schrock can be a partner in the company Seth Schrock and Schrock Farms then you have a many to many since a company can also have more than 1 partner.

[Rabbit]

I think the table layout is fine. But perhaps you can just mess around with visibility to reduce confusion for the users. Maybe gray out or make invisible one set of fields if there's exactly 1 customer and 1 partner.

[Seth Schrock]

@mshmyob I was just using my name as an example of the partner name being the same as the customer name, not actual data that would require a many-to-many relationship

@Rabbit - What would you gray out as there needs to be a name for both the customer and the partner?

[Rabbit]

The issue is that the users are confused when there's a partner and customer with the same name correct? To reduce the confusion for the user, you could gray out or hide the partner name when the name matches the customer and there's only one partner.

[Seth Schrock]

The confusion issue is when entering the information, not simply viewing it.

However, I think that I have come up with a way to make it simpler. I will have just one table (tblCustomers) that will have a Main_Customer_I D field. If the customer is just a single person with no partners, then the Main_Customer_I D will equal the value of the PK. If not, then it will be the PK value for the customer record who is the main customer. Any subforms that show customer balances can then be related to the Main_Customer_I D field instead of the PK field so that the proper balances show up.