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).
5 2635
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.
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.
@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?
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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 --...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |