Need help with setting up proper PK and FK relationship in SQL Studio

I have a table called Companies. The ID field is the PK (autoincrement), not the CompanyName field because you can have the same company name as long as it is a different address.

There is a Users table as well. This has a username field and a company field and an ID field (autoincrement) as it's PK. I was hoping to make the company field an FK for the CompanyName field in the Companies table. It won't let me because CompanyName is not a PK.

So how do I successfully set this relationship up?  Should I use some sort of concatenated keys for situations like this?
LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I was hoping to make the company field an FK for the CompanyName field in the Companies table.
Shouldn't be the opposite way? The company field in Users table should be the FK from Companies table so you just need to related that field with the Companies.ID column that's already a PK.
0
Scott PletcherSenior DBACommented:
You can't use CompanyName as a foreign key since it isn't unique.

But that also shows why the link to company from the user table should be the company id and not the name.  If it's the name,  you can't be sure which company it should link to.  The name can be looked up using the id key.

When the username is first entered, you could bring up a list of all companies that match the name entered and allow the user/screen operator to select a specific company from the list of names, then insert the corresponding company id in the user table.
0
BobHavertyComhAuthor Commented:
Hi Vitor, great to see you again. The set up is already as you say. The problem is that CompanyName in the Companies table is not a PK, because it won't always be unique. Companies can have the same company name, but have a different address, and that's valid to qualify it as a unique company. I think I will limit each company name to only one phone number, but it should be able to have multiple addresses as in reality, there are companies with the same exact name, but different location, and they are not the same company at all, and will have different users, etc.....
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BobHavertyComhAuthor Commented:
Hi Scott, thanks for answering. When I go to edit the relationship in SQL Studio, at first, on the left under Primary key table, I selected the Companies table, and then selected it's ID field, since that is the PK. On the right under Foreign key table, I selected the Users table, and then selected the Company field. But this won't work because the datatypes don't match. I tried to select the ID field for both Primary key table and Foreign key table, and I am running into this.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Users_Users1". The conflict occurred in database "mydata", table "dbo.Companies", column 'ID'.

This occurs during an insert to the Users table.

Surprisingly, this actually worked before, but after three records it no longer does, and keeps running into a conflict. So I KNOW there is some stupid little error in my design, even though it actually worked a few times.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Companies can have the same company name, but have a different address
Means that you need to normalize your database. You should create a CompanyAddress table to store all the addresses and relate them with the CompanyID so you'll know which address belongs to who.

I think I will limit each company name to only one phone number
Or you can do the same as I suggested for address. Or add a phone number for each address.

there are companies with the same exact name, but different location, and they are not the same company at all
As people have same names and live in different locations. How do you distinct them? By their identification number, right? Shouldn't a company have their own ID number also?
0
Scott PletcherSenior DBACommented:
>> I think I will limit each company name to only one phone number <<

But that's not real life.  You need to model the data as it actually is, not what's easiest for the db.  Otherwise you'll have people working around the system -- such as putting two phone numbers into a single column, or adding phone number(s) as comment -- and you'll end up with a complete mess.
0
BobHavertyComhAuthor Commented:
As people have same names and live in different locations. How do you distinct them? By their identification number, right? Shouldn't a company have their own ID number also?

There is an ID field that is the PK of the Companies table. As far as phone numbers and addresses, yes I suppose they should be split out for normalization, but I wonder about even bothering because that will add joins to the query, and it should be pretty rare that a companies actually have an identical name but different addresses. I wonder of it's worth it.
0
Scott PletcherSenior DBACommented:
It depends.  If you take orders, you'll need the original address for that order, so it will save you improve accuracy and save you lots of disk space to normalize.

You need to step back and do a quick logical design on all this.  Forget tables and keys.  Just look at the actual data you need and its relationships from a purely business standpoint.  Once that's done, you can turn the final design into physical tables -- and, btw, that's the easy part, by far.  The more difficult, and critical, part is the proper design up front.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wonder of it's worth it.
Yes it worth it. Open your mind to the database modeling world :)
Really, if you follow the normalization rules you'll see that will be more easy to achieve any task that you'll need now or in the near future. Start right and you won't have many issues later.
0
BobHavertyComhAuthor Commented:
>> I think I will limit each company name to only one phone number <<

But that's not real life.  You need to model the data as it actually is, not what's easiest for the db.  Otherwise you'll have people working around the system -- such as putting two phone numbers into a single column, or adding phone number(s) as comment -- and you'll end up with a complete mess.

I don't know. There's only one top administrator and primary contact for each company, so why not only one phone number? I'm thinking in terms of design theories, not so much entries that I will control through code where you cannot have identical company names and phone numbers. If a company in reality has two phone numbers, that does not signify two companies, it's one with multiple numbers, and I don't think we should allow that nor care about it. But there's nothing I can do about the addresses, because two addresses for the same company might NOT be the same company or a branch of it, as it would be with phone numbers, and instead would represent a new unique company. We can't be sure. But a different phone number is not enough to assure uniqueness, whereas sometimes a different address might.

Not an easy problem for me as I'm no expert.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you think about companies with representative offices? Each office should at least have a phone number. Headquarters usually have more than one phone number.
Also each office has their own address. Plus a company may have a deliver address different from the company address, right?
How are you thinking to solve these cases?
0
Scott PletcherSenior DBACommented:
>> . There's only one top administrator and primary contact for each company, so why not only one phone number? <<

Couldn't the administrator have a work# and cell#?  Or a backup#?  What if you can't get ahold of that person, might there not need to be a backup# of some kind?

Of course now you might be able to Google a number, and in some cases that would be a better answer than storing a number.
0
BobHavertyComhAuthor Commented:
Hi Scott
It depends.  If you take orders, you'll need the original address for that order, so it will save you improve accuracy and save you lots of disk space to normalize.

There are companies and there are customers. The address that goes on the orders is the customer's, not the company's. The company name goes on an order, and that info is linked to the Companies table. The customer's phone and address info goes on another table called "cust_info", which links to the "cust_info" field in the orders table. What I need to figure out is how to correctly set up a relationship between the Companies table, and the Users table, to link the value for the Company field entered on the Users table, to an existing entry on the CompanyName column on the Companies table.
0
BobHavertyComhAuthor Commented:
Couldn't the administrator have a work# and cell#?  Or a backup#?  What if you can't get ahold of that person, might there not need to be a backup# of some kind?

We never want OUR customers (companies) calling us, and we never want to call them. Users each have a phone number in their entries on the users table that can be referenced to contact each other or the customer, but there will only be one central phone number associated with the Company. We don't care about someone's emergency number, as we hope to never call them at all.
0
BobHavertyComhAuthor Commented:
Let me make this a lot easier for everyone to understand, and I apologize for not doing so before. My bad. This involves a delivery service product for restaurants that deliver. There are Restaurants, which would be OUR customers and use our software to mange THEIR deliveries. And then the Restaurants have customers, which they deliver to. Here are the tables. Users are employees of any given Restaurant using the software. I used identical names to signify the links.

Companies (ID, CompanyName, CompanyPhone, CompanyAddress etc...)
Users (ID, CompanyName, UserName, Password, Phone)
Orders (ID, CompanyName, UserName, Cust_Info)
Cust_Info (ID, CustomerPhone, Customer Address)

So I still haven't figured out how to get the CompanyName from the Users table to link to the CompanyName field of the Companies table. I don't care which fields I actually have to use to accomplish some sort of link, I just don't know how to solve this problem. It's usually easy, but as I've mentioned, there are a few complicating factors such as company name not being unique.
0
Scott PletcherSenior DBACommented:
You have to use CompanyID and not company name to do the link.  You'll never be able to accurately link by name, because there could be duplicates.  Even if a name was unique today, an identical name could be added later and cause the same problem.
0
BobHavertyComhAuthor Commented:
Hi Scott. I think it's because I don't properly understand what I am entering into SQL Studio.

On the Users table, in SQL Studio I select the CompanyName column and right click and select relationships. For the Primary key table, I chose Companies, and then in the field dropdown underneath, I chose ID, which is the PK name for the Companies table (int-autoincrement). For the Foreign key table, I selected Users and the CompanyName field. That obviously will not work because of a data type mismatch. The only field on the Users table with the same data type as the ID field on Companies is the ID field and PK on the Users table. I tried hooking these two up, and it let me, but it's not what I want as far as I can see. I'm almost looking for a value like ID.CustomerName from the Companies table to populate the CompanyName field of the User's table, but I don't know if that's right or how to accomplish that.
0
Scott PletcherSenior DBACommented:
Since there will be multiple matches for some names, you will have to select all matching names from the table, show them to the person entering the new name, and have them select the specific company name that matches that person.  When they do that, you will capture the CompanyID, rather than the name, to store in the Users table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobHavertyComhAuthor Commented:
Oh, so grab the CompanyID column value from Companies table and use it as the value for the dropdown option, and then use the CompanyName column value from the Companies table as the actual text that shows up in the choices?
0
Scott PletcherSenior DBACommented:
Yes, exactly.  But you'll need to show them other things about the company as well, such as address and phone number, so they can pick the right one.  After all, the company names are all identical, so that alone won't be enough to distinguish one company from another.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should review those tables. This is how should looks like after normalization (I've renamed the IDs for better understanding):
Companies (CompanyID, CompanyName, CompanyPhone, CompanyAddress etc...)
 Users (UserID, CompanyID, UserName, Password, Phone)
 Orders (OrderID, CompanyID, UserID, Cust_InfoID)
 Cust_Info (Cust_InfoID, CustomerPhone, Customer Address)

So from now you only need to join the respective ID fields to get the correspondent information from Company, User and Cust_Info.
0
BobHavertyComhAuthor Commented:
Yes, exactly.  But you'll need to show them other things about the company as well, such as address and phone number, so they can pick the right one.  After all, the company names are all identical, so that alone won't be enough to distinguish one company from another.

Great point. More problems. This turns out to be a more complex problem than I initially thought. Now I have to concatenate Company names and addresses in an html5 datalist.
0
BobHavertyComhAuthor Commented:
So from now you only need to join the respective ID fields to get the correspondent information from Company, User and Cust_Info.

I finally get what I was misunderstanding. The CompanyName field in the users table is a varchar(50) when it should be a an int to match the CompanyNameID field of the Companies table, as it is not supposed to store the CompanyName, but instead a link to it's ID from the Companies table. I was simply trying to pass string values between the CompanyName fields, but that's just never going to work.
0
BobHavertyComhAuthor Commented:
Thanks guys. Excellent advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.