• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

Database table design and Keys

T

tgabe213

2[H]4U
Joined
Aug 27, 2007
Messages
3,684
I've got a few tables that will form the database. I think my best bet is to set a primary and foreign key relationships between the two.

Here is a quick drawing on gliffy for reference. I'm trying to figure out how these are physically related. Is it with Primary/Foreign keys? The concept is a bit new to me, but I think I get the general understanding.

So how can I link [Customer] with [Referral] if I populate a table with SELECT * FROM Customer?

Thanks..
 
MS SQL Server 2005 is the database. Programming with asp.net and c# (web forms)
 
I think I might have gotten it (have not really tried anything).
The CustomerID of Customer is the PK, and I have the ReferralId of Referral linked as the PK.
 
Edit:

I'm not too sure what I wanted to do anymore. I had an idea in my head, but now I forgot, or it just didn't make sense to begin with.
 
Yes Primary and Foreign keys are how they will be related. Looks pretty decent from what I can tell for the simplicity of the database.

Each table needs a primary key. Its the field that uniquely identifies each row in the database. Foreign keys are a key in a table that is actually from another table (simply put).

You need to look at inner joins. That will help you a lot in the understanding of relating.
 
So, can ReferralID be both a primary and foreign key? It's the primary key for that table, but that is the value that is connected to the Customer table.

You need to look at inner joins. That will help you a lot in the understanding of relating.
Click to expand...

I just found that. I'll have a look tomorrow. Unfortunately, I have to leave to meet with my group for (ironically) my database class. Big project for data modeling sucks.

I'll have a look at this tomorrow morning. I don't think I really need to have a foreign key relationship (unless I'm totally forgetting my state of mind from earlier)
 
Yeah hang on, I notice some errors (I think). Let me study this for a couple more minutes.
 
Ok I think with the Customer table you don't ReferrallID in it. You want the CustomerID to go into the Referall Table via Foreign Key.

Also put the CustomerID in the Memo table via foreign key, and get rid of MemoID from the Customer table.

You will have Foreign key relationships. Its how databases work.
 
You don't have to include foreign key constraints right now. FK constraints can definitely be a good thing - they can definitely help minimize data anomalies and enforce referential integrity, etc. But they add complexity. If this is one of your first projects, my opinion would be to leave them out for now. (but definitely do define your primary keys as you have already done).

(and if you are just asking about how to actually describe your database in terms of PKs / FKs, you're on the right track!)

First, I see that you have used the field name ReferralID in the Customer table - however this might not be the best way to look at the relationship.

Is every referrer also a customer? In that case you might want the field in the Customer table to be ReferrerID - which would be the CustomerID of a different customer.

Anyway, as far as how you get a data set that contains both customer and referral information, you just want to use a join in your query - and there are different types of joins as well!

So if you wanted to see all the referrals that have been generated (But you don't care about your customers who have not referred anyone), with your existing layout you would go:


Code: 
--Returns only your customers with at least 1 referrer
--Also returns all referred people for each customer
SELECT
	*
FROM
	CUSTOMER
INNER JOIN REFERRAL ON CUSTOMER.ReferralID = REFERRAL.ReferralID

Now, let's say you wanted to get a list of all your customers and their referrals - but you also wanted to see the customers who have NOT referred anyone.

Code: 
--Returns all your customers, regardless of their status as an active referrer
SELECT
	*
FROM
	CUSTOMER
LEFT OUTER JOIN REFERRAL ON CUSTOMER.ReferralID = REFERRAL.ReferralID

Or... let's say you wanted to get a list of your deadbeat customers who never freaking refer their friends to you!

Code: 
--Gets a list of the people who have never referred anyone to you
SELECT
	*
FROM
	CUSTOMER
LEFT OUTER JOIN REFERRAL ON CUSTOMER.ReferralID = REFERRAL.ReferralID
WHERE REFERRAL.ReferralID IS NULL
 
Lazy, that makes 100% perfect sense! That's what I wanted to do, but sort of had it backwards. So if I did what you said (add customerID into the memo table, drop memoid out of customer table, etc) and did a select * from customer, it would refer to the existing memo's, and display them that way, correct? I'll definitely try this out tonight or tomorrow in the morning.

Calebb, thanks a lot! I've looked into the join a little bit on w3c, and it made a little sense (nothing too complex), so I'll definitely give that a shot.

When is it beneficial to use FK's rather than just a JOIN statement?
 
tgabe213 said:
Lazy, that makes 100% perfect sense! That's what I wanted to do, but sort of had it backwards. So if I did what you said (add customerID into the memo table, drop memoid out of customer table, etc) and did a select * from customer, it would refer to the existing memo's, and display them that way, correct? I'll definitely try this out tonight or tomorrow in the morning.

When is it beneficial to use FK's rather than just a JOIN statement?
Click to expand...

No, you need to do a inner join between the Customer table and the Memo table to get that.

You need FK's to use a JOIN statement. Its just how relationships work in databases. If no FK's are set up, the JOIN's just cant work as there is nothing to join.

So with your Customer and Memo table. The CustomerID in the Memo table will be a FK, buts its also a PK in the Customer table.

Then a simple SQL statement that would display everything in the customer table and every memo he wrote would be:

select * from CUSTOMER inner join MEMO on CUSTOMER.CUSTOMERID = MEMO.CUSTOMERID



Just remember SQL is stupid, you need to be very specific in telling it what to do. There are no assumptions with SQL. It does exactly what you tell it to do. So if you did a select * from customers, it will only select all from the Customers table, doesn't matter what kind of FK's you have set up.
 
Lazy_Moron said:
You need FK's to use a JOIN statement. Its just how relationships work in databases.
Click to expand...

This is true - but you don't need to code your FK constraint - i.e., MSSQL does not need to know (and doesn't care) what's the FK and what's the PK until you are actually executing your join.


Lazy_Moron said:
Just remember SQL is stupid, you need to be very specific in telling it what to do. There are no assumptions with SQL. It does exactly what you tell it to do. So if you did a select * from customers, it will only select all from the Customers table, doesn't matter what kind of FK's you have set up.
Click to expand...

QFT :)
 
calebb said:
This is true - but you don't need to code your FK constraint - i.e., MSSQL does not need to know (and doesn't care) what's the FK and what's the PK until you are actually executing your join.)
Click to expand...

Really? I guess I just got use to coding them, that I just never thought about leaving them out.
 
You guys are awesome! I love when I get quick responses.

Thank you so much. I actually can't wait to get to work tomorrow (well, sorta). :)
 
Microsoft SQL Server does and can use FK information when executing queries. My old boss wrote a pretty good blog post about one of the cases where the optimizer might use an FK relationship to improve performance. The server always cares because it needs to maintain that FK relationship, and must enforce the rules you asked for.

If the relationships in the diagram really marked "1-1" are one-to-one relationships, then they're incorrectly modeled. They're presently modeled as conditional one-to-one relationships.
 
Will this method for setting up the database allow me to delete individual referrals, but allow me to keep the customer's information?

Situation: We have tellers (bank) referring customer to our investment services. The memo table is just additional comments that will be added to each referral.
 
mikeblas said:
Microsoft SQL Server does and can use FK information when executing queries. My old boss wrote a pretty good blog post about one of the cases where the optimizer might use an FK relationship to improve performance. The server always cares because it needs to maintain that FK relationship, and must enforce the rules you asked for.

If the relationships in the diagram really marked "1-1" are one-to-one relationships, then they're incorrectly modeled. They're presently modeled as conditional one-to-one relationships.
Click to expand...

Conditional vs ____ ? Please explain if you could.
 
I'm a little confused on whether or not I need the FK in the Location table. That table will primarily be used to populate a drop down box in the referral add form.

Here is an updated version from what I've gotten from you guys.
I'm a little lost on what should be allowed to be NULL
 
select * from CUSTOMER inner join MEMO on CUSTOMER.CUSTOMERID = MEMO.CUSTOMERID
Click to expand...

This worked perfectly now that I have my keys setup. Now it is just a matter of getting familiar with coding the tables together to get only what I want to show.
 
tgabe213 said:
Conditional vs ____ ? Please explain if you could.
Click to expand...

Let's consider the relationships between REFERRAL and REFERRER, for example. To model this relationship correctly, you'll want to figure out if you can have a referral without a specific, single referrer. It's entirely possible that you don't; perhaps you model an unreferred customer as a conditional 1-1 relationship. If they're a walk-in, for example, they don't have a specific referrer. The rules you decide to use for this are dictated by the relality of your business, or the processes it uses to function.

If a REFERRAL always has a REFERRER, then there might be no REFERRER table; you might have the name and phone number of the REFERRER as required attributes of the REFERRAL table. This would mean that the REFERRER isn't actually an entity in the database.

If you want REFERRER to be an entity, then you can model REFERRER as a table as you have now, and add a ReferrerID column as a FK in the REFERRAL table to the REFERRER table. If this column is NOT NULL, then you've established that you must have a referrer if you have a referrer and that relationship has integrity.

Or, if you want REFERRER to be an entity, and still allow REFERRALS to exist without a REFERRER, you can create the same ReferrerID column as an FK in the REFERRAL table to the REFERRER table, but allow NULL values in the column. A NULL indicates the absence of a referrer.

The model you say is revised is precisely the same as the model you originally posted, by the way.
 
Thanks for the info.

The model is actually a new version that the original, but the link is the same since I just modified the original file.
 
I'm looking for something with a good reference to perform a join with more than just two tables (6 specifically)
 
For multiple-table joins, you just repeat the "JOIN" clause in your query.

Code: 
SELECT *
  FROM TableOne
 JOIN TableTwo on TableOne.Key = TableTwo.Key
 JOIN TableThree on TableTwo.Key = TableThree.Key
WHERE TabbleOne.Attribute = 'Interesting'
 
With a little help from VWD's Query builder, I came up with:
SELECT Location.LocationID AS [Branch Number], Customer.Name AS [Customer Name], Referrer.Name AS [Referrer Name],
Referral.ReferralDate AS [Date Referral Received], Referral.DateMet AS [Date Met With Client], Referral.SaleMade AS [Sale Made],
Referral.RevenueGenerated AS [Revenue Generated], Referral.ReferrerPaid AS [Referrer paid], Referral.Qualified, Advisor.Name AS [Advisor Name],
Memo.Note AS [Advisor Comments]

FROM Memo INNER JOIN
Customer ON Memo.CustomerID = Customer.CustomerID INNER JOIN
Referral ON Customer.CustomerID = Referral.CustomerID INNER JOIN
Advisor ON Referral.ReferralID = Advisor.AdvisorID INNER JOIN
Referrer ON Referral.ReferralID = Referrer.ReferralID INNER JOIN
Location ON Referrer.ReferrerID = Location.LocationID

ORDER BY [Branch Number]
Click to expand...

And it populates my 'GridView' (after a few name tweaks)!
Thanks for all the help so far!

I know I'll have a few more questions when it comes to inserting, etc.
 
tgabe213 said:
Well, here's where it gets difficult for me.
I need to select:

Customer.Name
Referrer.Name
Referral.DateMet
Referral.SaleMade
Referral.ReferrerPaid
Referral.Qualified
Referral.ReferralDate
Advisor.Name
Memo.Note

Does this mean my select statement would be the above
SELECT [above[ FROM [those tables]
JOIN table2 on table1
JOIN table3 on table2
etc
WHERE blah

?
Click to expand...

Is your table designed already?

Try this:

Code: 
SELECT
	Customer.Name
	Referrer.Name
	Referral.DateMet
	Referral.SaleMade
	Referral.ReferrerPaid
	Referral.Qualified
	Referral.ReferralDate
	Advisor.Name
	Memo.Note
FROM
	Customer
LEFT OUTER JOIN Referral ON Customer.CustomerId = Referral.CustomerId
LEFT OUTER JOIN Memo ON Customer.CustomerId = Memo.CustomerId
LEFT OUTER JOIN Advisor ON Referral.ReferralId= Advisor.ReferralId

Or you might try RIGHT OUTER JOIN - compare the data set you get and decide what you need.

You probably don't want INNER JOIN for any of these queries, but I could be wrong -
 
tgabe213 said:
With a little help from VWD's Query builder, I came up with:

SELECT Location.LocationID AS [Branch Number], Customer.Name AS [Customer Name], Referrer.Name AS [Referrer Name],
Referral.ReferralDate AS [Date Referral Received], Referral.DateMet AS [Date Met With Client], Referral.SaleMade AS [Sale Made],
Referral.RevenueGenerated AS [Revenue Generated], Referral.ReferrerPaid AS [Referrer paid], Referral.Qualified, Advisor.Name AS [Advisor Name],
Memo.Note AS [Advisor Comments]

FROM Memo INNER JOIN
Customer ON Memo.CustomerID = Customer.CustomerID INNER JOIN
Referral ON Customer.CustomerID = Referral.CustomerID INNER JOIN
Advisor ON Referral.ReferralID = Advisor.AdvisorID INNER JOIN
Referrer ON Referral.ReferralID = Referrer.ReferralID INNER JOIN
Location ON Referrer.ReferrerID = Location.LocationID

ORDER BY [Branch Number]
Click to expand...

And it populates my 'GridView' (after a few name tweaks)!
Thanks for all the help so far!

I know I'll have a few more questions when it comes to inserting, etc.
Click to expand...

Query builders can be useful, but make sure you use them carefully and really think about the query it designs for you!

In this query, if you don't have a memo for a customer, you won't get any results for that customer - no referrals, no referrers, etc -
 
Hm. I see that now. How can that be avoided? Should the FROM be FROM Customer? Memo is what it put there as default. That's all I wasn't sure about..

That sort of complicates things because on the add form, the additional information sections will not populate the Memo.Note field. An adviser will as time goes on.
 
There we go. I basically just switched around Memo and Customer to:
"SELECT Location.LocationID AS [Branch Number], Customer.Name AS [Customer Name], Referrer.Name AS [Referrer Name],
Referral.ReferralDate AS [Date Referral Received], Referral.DateMet AS [Date Met With Customer], Referral.SaleMade AS [Sale Made],
Referral.RevenueGenerated AS [Revenue Generated], Referral.ReferrerPaid AS [Referrer Paid], Referral.Qualified,
Advisor.Name AS [Advisor Name], Memo.Note AS [Advisor Comments]
FROM Customer
INNER JOIN Memo ON Customer.CustomerID = Memo.CustomerID
INNER JOIN Referral ON Memo.CustomerID = Referral.CustomerID
INNER JOIN Advisor ON Referral.ReferralID = Advisor.AdvisorID
INNER JOIN Referrer ON Referral.ReferralID = Referrer.ReferralID
INNER JOIN Location ON Referrer.ReferrerID = Location.LocationID
ORDER BY [Branch Number]"
Click to expand...

Do you see any other problems this might pose (if a field is empty). Not all of these will obviously be filled by default. Should I just allow NULL across the board? :rolleyes::confused:

Also, there is a chance that a customer can have more than 1 comment associated with them. I just added a second 'Note' in the Memo table, with a duplicate CustomerID (I have 2 test entries in there). It is dislpaying the referral two times, with all the same information except that comment.
 
tgabe213 said:
... because on the add form, the additional information sections will not populate the Memo.Note field. An adviser will as time goes on.
Click to expand...

This is why I chose LEFT OUTER joins instead of INNER JOINS in the hand written query -

Try it - all you'll have to do is fix the select aliases (AS [blah]) for each line in the select statement -
 
Why would I have to fix the aliases? Why is that all I need to do? Don't I need to change INNER JOIN to LEFT OUTER JOIN?
 
Changed to
SELECT Location.LocationID AS [Branch Number], Customer.Name AS [Customer Name], Referrer.Name AS [Referrer Name],
Referral.ReferralDate AS [Date Referral Received], Referral.DateMet AS [Date Met With Customer], Referral.SaleMade AS [Sale Made],
Referral.RevenueGenerated AS [Revenue Generated], Referral.ReferrerPaid AS [Referrer Paid], Referral.Qualified,
Advisor.Name AS [Advisor Name], Memo.Note AS [Advisor Comments]
FROM Customer
LEFT OUTER JOIN Memo ON Customer.CustomerID = Memo.CustomerID
LEFT OUTER JOIN Referral ON Memo.CustomerID = Referral.CustomerID
LEFT OUTER JOIN Advisor ON Referral.ReferralID = Advisor.AdvisorID
LEFT OUTER JOIN Referrer ON Referral.ReferralID = Referrer.ReferralID
LEFT OUTER JOIN Location ON Referrer.ReferrerID = Location.LocationID
WHERE Referral.CustomerID = Customer.CustomerID
ORDER BY [Branch Number]"
Click to expand...

Note the LEFT OUTER instead of INNER, and the WHERE clause (because a customer without a referral was showing).

Now I'm pretty sure I'll be able to have a drop down box of customers that have already been referred (aka, in the DB already). [Any way of having them delete themselves after a certain period of time?]
 
Hm. Ok, this part is going to drive me nuts.
The Location table is going to store all of our location numbers/name (and the referralid FK).
I want the drop down box on the add referral form to populate with the list of available locations to select from. My only issue is that, I need to put in a value for ReferrerID in the Location table, but not every location may have a referrer that has made a referral.....

???
 
tgabe213 said:
Why would I have to fix the aliases? Why is that all I need to do? Don't I need to change INNER JOIN to LEFT OUTER JOIN?
Click to expand...

Sorry - I was too terse - there were two things I meant:

1. Scrap your query and use the query I wrote (takes care of replacing inners with outers)
2. Modify this query to use your existing table aliases so your existing bindings work.
 
tgabe213 said:
Hm. Ok, this part is going to drive me nuts.
The Location table is going to store all of our location numbers/name (and the referralid FK).
I want the drop down box on the add referral form to populate with the list of available locations to select from. My only issue is that, I need to put in a value for ReferrerID in the Location table, but not every location may have a referrer that has made a referral.....

???
Click to expand...

You've found another flaw in your data model. The Location table you've modeled doesn't store all location numbers and names. It stores only the ones that are involved in referrals. If you want a reference table, you need to model one and then populate it in your system, and find a way to maintain it. This is called a "reference table", or "minor entity table".
 
Thanks Mike. I'll be looking into that very soon. That will probably be one of my next steps in this project (before it gets too far).
 
Please remind me again why it is better to separate out the data into different tables rather than all my information in 1 table. It just seems like it would make it a lot easier on myself having everything (other than the location table) in 1 large table...
 
tgabe213 said:
Please remind me again why it is better to separate out the data into different tables rather than all my information in 1 table. It just seems like it would make it a lot easier on myself having everything (other than the location table) in 1 large table...
Click to expand...

If you put all of your data in a single table then you're completely missing out on the flexability and power behind a relational-database.

It allows you to find your data faster and reduces all of the duplicate data you would run into if you had everything in one table... If you put all of your data into a single table you would be VERY hard pressed to find a primary key to work with. This would make finding your data next to impossible... or at least a very arduous journey.

Relational databases allow you to use relationships to link your data together in ways that make sense, and keep seperate data that is important, but doesn't fall into an easily-definable relationship.

It allows you to enforce referential integrity and control the data your users can access and how they can access that date.

Also, multiple tables reduces the risk of a single user deleting all of your data somehow... and even if they do somehow manage to delete a table you could reconstruct it easier than trying to reconstruct EVERYTHING in a single table (after you wipe their blood off your hands, of course).

There are many more reasons I can't think of right now, but I'm sure many others will be able to explain the reasons why Single large-table databases are BAD!
 
You must log in or register to reply here.
Back
Top