By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Best Design Question

Seth Schrock
Expert 2.5K+
P: 2,937
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).
Sep 21 '17 #1
Share this Question
Share on Google+
5 Replies


mshmyob
Expert 100+
P: 903
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.
Sep 21 '17 #2

Rabbit
Expert Mod 10K+
P: 12,357
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.
Sep 21 '17 #3

Seth Schrock
Expert 2.5K+
P: 2,937
@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?
Sep 25 '17 #4

Rabbit
Expert Mod 10K+
P: 12,357
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.
Sep 25 '17 #5

Seth Schrock
Expert 2.5K+
P: 2,937
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_ID field. If the customer is just a single person with no partners, then the Main_Customer_ID 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_ID field instead of the PK field so that the proper balances show up.
Oct 13 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.