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  2645  mshmyob 904
Recognized Expert Contributor
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 12,516
Recognized Expert Moderator MVP
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?
Rabbit 12,516
Recognized Expert Moderator MVP
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_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.
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 -- especially in
multi-module programs?
Here's one approach:
--- main.py ---
import my_globals as g
import somefuncs
|
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 our client. One
column which I would like to use is called 'used_by',
which would store information about which business
sections (Financial Management Branch, Human Resources
Branch, etc.) use a particular application. Often
|
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 card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check, etc.)
If I place the CreditCardNum and CheckNum fields into a payments
|
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 commission . The
commission could be given per EITHER sales amount of : Group of Products OR
Group of Brand. e.g : the data example :
For one salesman_A :
product_1, product_2, product_3 etc.. => sales = $100 - $200 =>
|
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 the processes (link to business
process) including persisting the user input. Is this the best way? Or
maybe have a persist class for each form which will handle the persistence
(serializing)? Any ideas would be helpful.
Thanks
| | |
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 ints. The size of the arrays would depend
on some external value (going from 0 to around 1000 max). I would have an
array of max 255 of these classes/structs (in most cases it will be less
then 5 however)
Since there's no real business logic my...
|
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 combination of
all other user fields.
So it's seductive to use the card id as the primary key.
This card allows access to certain places and all access is logged.
|
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 database calls (using a
separate DB handler class). "combo1" contains a list of countries and is
fairly static, it can be added to but no other external events cause a
change in its population. "combo2" however is populated with a list of
options...
|
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 foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
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
"new" contract.
I have a method in my contract class called "Save" which is called like
this...
dim oContract as new Contract
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| | |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
