473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Best Design Question

Seth Schrock
2,965 Expert 2GB
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
5 2635
mshmyob
904 Expert 512MB
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
12,516 Expert Mod 8TB
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
2,965 Expert 2GB
@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
12,516 Expert Mod 8TB
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
2,965 Expert 2GB
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

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

Similar topics

2
by: Robin Munn | last post by:
OK, here's another software design question, one that's been bugging me for a while now. What do y'all think is the best way to handle program-level globals, such as configuration option --...
0
by: James Walters | last post by:
Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for...
4
by: Opa1 | last post by:
I am designing and product ordering database and have a design question. I have a Payments table which records all payments for a given OrderID. There are several payment methods (cash, credit...
1
by: Krist | last post by:
Hi All, There is some additional info I forget on this same topic I just posted. I have a database design question, pls give me some help.. I want to define tables for salesman's sales target...
0
by: Dan | last post by:
Hi, I have a design question on what is the best way to design saving user input from user interface forms? I was thinking on having a process control class for each form which would handle all...
26
by: phoenix | last post by:
Hello, I've got a design question. I need to keep track of some variables and I am planning to put them inside a class or struct. Basically I'm talking about 10 bools, 20 ints and 2 arrays of...
3
by: reageer | last post by:
Hi all, I have a design question: I have a bunch of users (name, address, zip, etc.). They are assigned a card with a specific id. The only thing unique is this card id, or probably the...
0
by: Paul Hadfield | last post by:
I'm looking for thoughts on the "correct" design for this problem (DotNet 2.0 - in winforms, but that's not so important). I've got two combo boxes (combo1 and combo2), both are populating via...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
29
by: Brad Pears | last post by:
Here is a simple OO design question... I have a Contract class. The user can either save an existing contract or they start off fresh with a blank contract, fill in the data and then save a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.