Database table design and Keys

tgabe213 · Sep 24, 2008

Maybe it's just my design of the database that is driving me nuts.That, and the unfamiliarity with the join statements, and trying to figure out how to select certain things from certain tables and update/delete certain things from certain tables.

calebb · Sep 24, 2008

tgabe213 said:
Maybe it's just my design of the database that is driving me nuts.That, and the unfamiliarity with the join statements, and trying to figure out how to select certain things from certain tables and update/delete certain things from certain tables.

What if someone wanted to save their fax number into your database? Or more than 1 cell phone number?

What if a user (referral) had 3 different contact times that were good?

What if a user wanted two locations? (business/home)

What if a user wanted three addresses (business/home/cabin)

What if three users were siblings and all lived at the same residence? (they could all have the same idAddress)

How about this?

http://www.benefiel.net/hosted/hardocp/tgabe213.vsd

In this schema, every person is a User.

A User can be a customer.
A User can be an Advisor
A User can be a Referrer
A User can be a Referral

1 Referrer can have many Referrals
1 Referral can have many Referrers (possibly eliminating the need for the Advisor table - this sounds like a co-referrer type table)

genDate can be populated in your SQL code when you do your update - just use GetDate()

i.e.,

Code:

UPDATE 
	USERS 
SET
	FullName = 'John Doe',
	SpouseName = 'Jane Doe'
	genDate = GetDate()

(this way you know when the record was updated last)

Now, the whole conversation about FK constraints.

Let's say you did build your database with all the FK constraints that I modeled in this graphic:

Now, down the road, you try to delete some random address. One of two things can happen:
1. If your SQL server is set for cascading deletes, every user that was at that address and then every memo, referral, advisor relating to that user would also be deleted
2. If you SQL server is not configured for cascading deletes, then you would not be able to delete that address - the SQL exception would inform you of the FK Constraint violation.

--> This is what they mean by referential integrity.

Since you are newer to DB designs, it could be argued that you could leave the FK constraints out initially and just use your joins to retrieve the data...

If you download that .vsd and the free Visio viewer from microsoft.com you can see the rest of the field definitions.

And, last disclaimer, this is definitely not a fully normalized database! However, for your uses and getting familiar with db design, it should work great for you.

tgabe213 · Sep 24, 2008

Calebb, I really cant thank you enough for all this. Not only for the information, but for taking all of the time to throw this thing together (even though it may be quick for you).

I guess the thing that really confuses me the most is when information is added/deleted/modified when you are dealing with Keys. For example, if I add a new User to your setup, the way the information (idUser) that gets added into REFERRAL_ADVISOR is through the join statements, right? The same would apply to deleting and modifying?

Yes, this all is pretty new to me. Starting next week we are starting Logical Database Design and the Relational Model in the textbook, which is consisting of referential integrity, PK/FK, etc (with 2 chapters on SQL after that). So I think this is great timing to be reading this chapter while having your diagram to refer to (personal example). I really think that this will work great for me, Im just going to have to slightly modify my referral add form (just add a few fields since no SQL has been written yet), and really rethink the way that the computer illiterate administrators (for this project) will do their work.

I'm also going to have to figure out how to create a table like REFERRAL_ADVISOR. Yea, it has 1 value of idReferralAdvisor, but how do you add those foreign keys? You add them into the table as another data type, and link them? Am I over thinking this? (I just found the details of each item in the table (thank you so much again). I'm really going to study this and use it as a good reference when reading my textbook.

calebb · Sep 25, 2008

tgabe213 said:
I guess the thing that really confuses me the most is when information is added/deleted/modified when you are dealing with Keys. For example, if I add a new User to your setup, the way the information (idUser) that gets added into REFERRAL_ADVISOR is through the join statements, right? The same would apply to deleting and modifying?

Yes - you can use joins in an update statement. But this, like using FK constraints, adds complexity. I would recommend writing prepared statements (stored procedures) for each action you wish to accomplish. (but you can put your SQL into your application as well - definitely up to you - I just prefer using sprocs for organization / and slight performance increase)

To start, make a list of each action that needs to happen.
Figure out what data you will need in order to carry out each action.

For example, adding a new user:
idUser is the primary key and can be setup to automatically create a new idUser

So let's say you want a stored procedure to add a new user:

We'll call it addUser: it needs inputs of FullName and SpouseName

The way to describe this sproc to someone (another programmer or your documentation) would be:

[dbo].[addUser] @FullName, @SpouseName

Just execute this in Management Studio to create this sproc:

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[addUser] 
	@FullName VARCHAR(50), 
	@SpouseName VARCHAR(50)
AS
	INSERT INTO USERS
		(FullName, SpouseName, genDate)
	VALUES
		(@FullName,@SpouseName. GetDate())  --GetDate() gets the current datetime

And then you'd need a second sproc to update the user - name it UpdateUser perhaps and add another input for @idUser.
(or if you're clever, you can combine an add/update into the same query)
(or if you're really clever and using SQL 2008, just use the MERGE keyword instead - MERGE tries to do an UPDATE first. If the update fails (no rows are modified), it does an insert)

Anyway, back to your question... updating additional fields.

Well, now you have a user - so you can create an address for them. The only problem is you need the idUser of the user you just created.

Two options:
1. Write a getUser sproc that passes in a name as the input. (might be needed anyway)
2. Add one more line to end of the addUser sproc:
SELECT @@IDENTITY AS idUser
(now when you run addUser, use something like ExecuteScalar() in .NET and it will return the idUser as an INT and you can immediately use it to start adding more data for this user based on all the other form data the user presumably filled out).

i.e., [dbo].[addAddress] @idUser, @AddressType, @Address1, @Address2, @Address3, etc

You will end up with a decent size pile of sprocs when you are done, but you know how each of them works, and each does a fairly simple job.

I'm also going to have to figure out how to create a table like REFERRAL_ADVISOR. Yea, it has 1 value of idReferralAdvisor, but how do you add those foreign keys? You add them into the table as another data type, and link them? Am I over thinking this? (I just found the details of each item in the table (thank you so much again). I'm really going to study this and use it as a good reference when reading my textbook.

So if you decide to add foreign key constraints it will make a few things trickier: That's the cost of the referential integrity

You can no longer add an Address to the USER_ADDRESS table unless, the user already exists in the USERS table. (probably not a bad thing!) But if a customer fills out a form that contains their user info and their address, you better make sure your code takes care of inserting the record into the users table before it adds the address record

I don't have a copy of Management Studio close, but here's the basic idea for adding a FK Constraint:

Code:

ALTER TABLE USER_ADDRESS
ADD CONSTRAINT FK_USER
FOREIGN KEY (idUser) 
REFERENCES USERS (idUser) 
ON DELETE CASCADE

This FK constraint is setup so that if you ever delete an Address that belongs to anyone, it automagically deletes anyone who was at that address.

And again... this is not necessary right now. It's a best practice, it can improve performance, but for starting out, I would not at the FK Constraints initially

And, side tip - not sure if you've executed a SPROC in .NET yet - or if you're even using .NET - but here's how you do it:

Code:

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "addUser";  // SPROC Name
            cmd.Parameters.AddWithValue("@UserName", "John Doe"); 
            cmd.Parameters.AddWithValue("@SpouseName", "Jane Doe");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = m_conn; //your connection

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();


            try
            {
                m_conn.Open();
                da.Fill(ds);
                //alternately: skip the dataadapter and dataset
                // int intIdUser = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                //ExecuteScalar just gets the very first item returned by your query
                //and disposes of everything else.  It's much faster / lower overhead
                //than a dataadapter...
            }
            catch (Exception ex)
            {
                throw ex;  //to be handled higher up the stack
                //or Console.WriteLine(ex.Message.ToString());
            }
            finally  
            {
                //best practice to close your connection and 
                //dispose of your dataadapter in the finally block 
                //in case of exception!
                m_conn.Close();
                da.Dispose();
            }

int intNewIdUser = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
          
//run some more code with your new idUser - like add an address for them

tgabe213 · Sep 25, 2008

Thanks again for all of this.

I'm using .NET. I'm using VWD 2008, except our SQL Server is 2005, so any database work has to be done on the server directly. I can't do it within VWD then import it. I'm going to have to look and see if I can find a free student copy of VS2005 (I think I can get one still). I took the day off today because I had a lot of other things I needed to take care of.

I think first thing tomorrow is to start organizing and deciding what and how everything will work (interfaces, etc). Then I will use your information from there.

tgabe213 · Sep 29, 2008

Starting work on this today.

Is there a way to print just the details you provided within Visio? I'm using the actual app of Visio, not just the viewer.

I decided to leave out the FK's.

tgabe213 · Sep 29, 2008

I think I'm going to need a little help getting started here. It's semi confusing at first since everyone is a user.

So for my add form, it's going to start out with the referrer's information. What if this is a new user? Existing user?

To start off, this user will need to be created before anything else, right?

Here are 4 screenshots of what the add form is. 3 steps to break apart the information that all is returned in step4 nice and clean. From there, the information will be submitted (this is a rough copy).

http://www.mytestmule.com/projects/Step1.png
http://www.mytestmule.com/projects/Step2.png
http://www.mytestmule.com/projects/Step3.png
http://www.mytestmule.com/projects/Step4.png

That is the way the add form sits right now. I'm wondering what my best approach to this is (since each user has their own ID, etc).

tgabe213 · Sep 29, 2008

Also, here is the 'Show Referrals' and the Edit/Admin page.

http://www.mytestmule.com/projects/CurrentReferrals.png

http://www.mytestmule.com/projects/Edit.png
For edit, the GridView table on the left of the page will display by default. When the user (admin) selects the SELECT link at the left of each row, then the details of that referral will display on the right. Only 3-5 columns will appear in the GridView table on the left, while EVERYTHING will appear on the right available for editing.

tgabe213 · Sep 29, 2008

calebb: A few questions regarding your design.
Why have a NumberType in USER_CONTACTNUMBER? How can I insert my inputted values into there? (How would I know what textbox corresponds with each type)

Same issue with AddressType in USER_ADDRESS.

Playing around with this and reviewing the diagram more and more, I see exactly what you were thinking here. When submitting the summary page, can I have multiple insert statements (sproc), or does it just matter the order that they are being inserted? I know, when creating a new referral with a new customer and new referrer, their information will need to be created in USERS, before anything else is inserted.

tgabe213 · Oct 1, 2008

Is it better to just design my interface, and then worry about the backend later? I think I am over thinking the adjustments needed for my add form since I'm keeping the database design in mind. I know I'll have to at least have some thought of it, but not make it my primary concern?

tgabe213 · Oct 2, 2008

Still working on this. Mainly on the UI end right now. Got my add form all complete today (a few things took some reasearch). I'll be making a few changes to the admin's edit table, which will be quick, then comes the data access...Gahh

tgabe213 · Oct 3, 2008

Alright, now comes time for the assistance.

I'm going to try to figure it out on my own, but I need a little assistance with something.
In the table USERS, I added a column named Type to identify as an Advisor, Customer, or Referrer. In one of my tables (.net GridView), I want to display the name of Customers, and the name of Referrer's (See http://www.mytestmule.com/projects/CurrentReferrals.png).

How am I going to do this? For the Customer column, I want to select USERS.FullName from USERS WHERE USERS.Type = "Customer". How do I assign that to the column heading Customer, and how do I assign the USERS.Type = "Referrer" to the Referrer column?

tgabe213 · Oct 3, 2008

SELECT USERS.FullName, USERS.idUser, USERS.Type
WHERE USERS.Type = "Customer" AS [Customer Name]
AND USERS.Type = "Referrer" AS [Referrer Name]

That's what I'm looking to do, but that obviously doesn't work.

Ideas?

calebb · Oct 3, 2008

tgabe213 said:
That's what I'm looking to do, but that obviously doesn't work.

Ideas?

You can't do that - also, you're missing a FROM in your SQL statement.

You need two distinct data sets from the USERS table - one where USERS.Type = Customer and one where USERS.Type = Referrer.

So.. you need to join the USERS table to itself!

Code:

SELECT 
	U1.idUser AS [Customer idUser], 
	U1.FullName AS [Customer Name], 
	U2.idUser AS [Referrer idUser],
	U2.FullName AS [Referrer Name]
FROM USERS U
--Note!  I did two things in this join.  I only joined to the second USERS (U2) where the type is referrer!
INNER JOIN USERS U2 on U2.idUser = U1.idUser AND U2.Type = "Referrer" 
--Note! I only use WHERE on the first USERS (U1) to limit it to customers. 
WHERE 
	U1.Type = "Customer"

Note that U1 only has customer info. U2 only has referrer info belonging to the customers in U1. Good luck!

tgabe213 · Oct 3, 2008

I think I understand how the U1 and U2 work, but I don't see how that can be implemented in here.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "U1.idUser" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Referrer'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "U1.Type" could not be bound.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Customer'.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "U1.idUser" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "U1.FullName" could not be bound.

calebb · Oct 3, 2008

tgabe213 said:
I think I understand how the U1 and U2 work, but I don't see how that can be implemented in here.

Oh I just looked at your query, not the schema I wrote up

I see that referrals are in a different table...

Actually, I don't see "Type" anywhere -

Could you update us with what your schema currently looks like?

You probably just need to change the join to USER_REFERRALS UR

tgabe213 · Oct 3, 2008

Take a peek at my post before the query. I added the column type to USERS, to distinguish the type of user they are (since the user information like name, doesn't reside in USER_REFERRALS).

Or am I looking at this the wrong way?

tgabe213 · Oct 6, 2008

What is the U and UR that you are writing? In the first one, it seemed to be a typo, but now I see it again.

USERS
FullName
SpouseName
Type
genDate

I've current got 2 entries in the USERS table. One that has a type of Customer, another that has a Type of Referrer. Take a look here about how I want to have the entries of both customer and referrer to be in the same table (actually, I'll need the 3rd type, Advisor as well).

Can you have multiple select statements in 1 query?

calebb · Oct 6, 2008

tgabe213 said:
What is the U and UR that you are writing? In the first one, it seemed to be a typo, but now I see it again.

USERS
FullName
SpouseName
Type
genDate

I've current got 2 entries in the USERS table. One that has a type of Customer, another that has a Type of Referrer. Take a look here about how I want to have the entries of both customer and referrer to be in the same table (actually, I'll need the 3rd type, Advisor as well).

Can you have multiple select statements in 1 query?

The U1, U2, U and UR are table aliases.

i.e.,

Code:

SELECT 
	U1.idUser AS [idUser from the first USERS table], 
	U1.FullName AS [FullName from the first USERS table], 
	U2.idUser AS [idUser from the second USERS table],
	U2.FullName AS [FullName from the second USERS table]
FROM USERS U
INNER JOIN USERS U2 on U2.idUser = U1.idUser

Compare with:

Code:

-- BAD!  This will throw several ambiguous column errors
SELECT 
	idUser AS [idUser from the first USERS table], 
	FullName AS [FullName from the first USERS table], 
	idUser AS [idUser from the second USERS table],
	FullName AS [FullName from the second USERS table]
FROM USERS
INNER JOIN USERS on USERS.idUser = USERS.idUser

See why the second one is bad? How do I know which idUser I'm interested in? Or which FullName? Plus, using table aliases makes your code less wordy / easier to read.

Yes, you can do multiple selects in the same statement - but I don't think this is what you want to do. (I could be wrong!)

Ok, on to your question

USERS
FullName
SpouseName
Type
genDate

You DEFINITELY need an idUser field! This will be much, much more tedious to generate idUsers on-the-fly

So, I'll assume you're going to make that change:

USERS
idUser
FullName
SpouseName
Type
genDate

Now, what is your exact question?

I see you took out the USER_REFERRALS table. This might seem like it will make things easier right now, but what if you want a user (say, John Doe) to be an Customer and a Referrer? With your new schema, the only way to do this is to have John Doe in the USERS table twice. What if he gets a divorce? And you update his SpouseName in one row, but not the other? You'll be embarrassed when he comes in with his new wife and up pops his old spouses name!

But, other than that plug, I'm not going to force a design decision down your throat

So, since I don't know your exact question, I'm going to assume this is it:

Q: How do I populate combo boxes for Advisor and Referrers for a particular customer. (i.e., in this screenshot, Customer is a textbox but referrer and advisor are comboboxes.

A: You will need the FullName and idUser for the text/value pair for your combobox binding:

Part 1:
SELECT DISTINCT idUser from USERS;

Part 2:
With each idUser, you will run:

Code:

SELECT
	U2.idUser AS [idUser Of Referrer],
	U2.FullName AS [FullName Of Referrer]
FROM
	USERS U1
INNER JOIN USERS U2 ON U2.idUser = U1.idUser and U2.Type = 'Referrer'
WHERE U1.idUser = @idUser   -- replace this with the idUser of the CUSTOMER
ORDER by genDate ASC

And, for an exercise for you, I will leave out the code to get the list of Advisors for a particular user.

tgabe213 · Oct 6, 2008

Whoops. I totally forgot to include idUser in the USERS table, sorry. And I didn't get rid of the user_referrals table. I simply added type to USERS to identify what Type of user each entry is.

My question is...Look at the screenshot that I posted (the same one you did too). Notice that there are column headings on Customer Name, Referrer Name, and Advisor name. All 3 of these names reside within the USERS table.

Gotta dive into something else quick before I get to this, so I'll be back here soon I'm sure

.

calebb · Oct 6, 2008

tgabe213 said:
Whoops. I totally forgot to include idUser in the USERS table, sorry. And I didn't get rid of the user_referrals table. I simply added type to USERS to identify what Type of user each entry is.

My question is...Look at the screenshot that I posted (the same one you did too). Notice that there are column headings on Customer Name, Referrer Name, and Advisor name. All 3 of these names reside within the USERS table.

Gotta dive into something else quick before I get to this, so I'll be back here soon I'm sure .

Yep, my answer is still the same - you just gotta join the table USERS to the table USERS to get the result set you are looking for. (as in my previous reply).

Try it in management studio!

Good luck!

tgabe213 · Oct 6, 2008

I've been working with it in Management Studio already.

You say to replace the @idUser with the idUser of the customer. What if I want to display ALL of the customers, instead of just one? The way I see what you are saying, is that I replace that @idUser with 1,2,3,etc whatever the id is for customers.

Even when placing a 1 there (id = 1 is a customer right now), I get an error:
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'genDate'.

It gives me that regardless of what I put in there. If I disregard that ORDER BY clause, the column headings show, but no data is filled. (and just so you know, I'm not waiting for a response. I am playing around looking for examples to reference in between your posts

)

calebb · Oct 6, 2008

tgabe213 said:
I've been working with it in Management Studio already.

You say to replace the @idUser with the idUser of the customer. What if I want to display ALL of the customers, instead of just one? The way I see what you are saying, is that I replace that @idUser with 1,2,3,etc whatever the id is for customers.

Even when placing a 1 there (id = 1 is a customer right now), I get an error:
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'genDate'.

It gives me that regardless of what I put in there. If I disregard that ORDER BY clause, the column headings show, but no data is filled. (and just so you know, I'm not waiting for a response. I am playing around looking for examples to reference in between your posts )

Ambiguous because you have 2 genDates

One in table U1 and one in table U2 (a different copy of the USERS table)

So make it U1.genDate

If you want all users, remove idUSer = @idUSer from the where clause

tgabe213 · Oct 6, 2008

Thanks. That worked (and makes sense why it does, duh). That result is displaying just the idUser and FullName of those that have type = "Referrer" so, thinking it might be simple, I tried this:

SELECT
U1.idUser AS [idUser Of Referrer],
U1.FullName AS [FullName Of Referrer],
U2.idUser AS [idUser Of Customer],
U2.FullName AS [FullName Of Customer]
FROM
USERS U1, U2
INNER JOIN USERS U2 ON U2.idUser = U1.idUser and U2.Type = 'Referrer'
INNER JOIN USERS U1 ON U1.idUser = U2.idUser and U1.Type = 'Customer'
ORDER by U1.genDate ASC

I bolded the changes. Basically, I added in just the opposite of what you had, hoping it would give me the result of the customers as well. It gives me an error of "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
which highlights the SELECT word when clicking on the error. Hmmm.

tgabe213 · Oct 6, 2008

I went back to your example a few posts back, and changed it to meet what I have...

Code:

SELECT
	U1.idUser AS [IdUser from tbl1],
	U1.FullName AS [FullName from tbl1],
	U2.idUser AS [idUser from tbl2],
	U2.FullName AS [FullName from tbl2]
FROM USERS U1
INNER JOIN USERS U2 on U2.idUser = U1.idUser

and it worked! Edit: Just 1 problem though. Each row is showing the information two times. It's not actually working how I want it to be, but the data is displaying. Screenshot comingNow to figure out getting advisor in here...

After I get Advisor working, what would you recommend to add in all of the other fields? Should I make another SELECT query for those remaining fields?

Here is my output of the above query.

idtbl1 -------name table1 -------------idtbl2----------name table2
1--------------Joe Customer---------------1-------------Joe Customer
2--------------Josh Referrer----------------2-------------Josh Referrer

Everything is displaying twice in both columns...Gahhh, this stuff is really frustrating.

calebb · Oct 6, 2008

tgabe213 said:
I went back to your example a few posts back, and changed it to meet what I have...

and it worked! Edit: Just 1 problem though. Each row is showing the information two times. It's not actually working how I want it to be, but the data is displaying. Screenshot comingNow to figure out getting advisor in here...

After I get Advisor working, what would you recommend to add in all of the other fields? Should I make another SELECT query for those remaining fields?

You can do it however you want

Are you manually populating the combo boxes or using the drag/drop designer method?

To be honest with you, I'm much more familiar manually populating datatables/datasets and then binding them to textboxes and comboboxes...

So, to get your users:

SELECT
U1.idUser AS [IdUser from tbl1],
U1.FullName AS [FullName from tbl1]
FROM USERS

To get referrers

SELECT
U1.idUser AS [IdUser from tbl1],
U1.FullName AS [FullName from tbl1],
U2.idUser AS [idUser from tbl2],
U2.FullName AS [FullName from tbl2]
FROM USERS U1
INNER JOIN USERS U2 on U2.idUser = U1.idUser AND U2.Type = 'Referrer'

To get Advisors

SELECT
U1.idUser AS [IdUser from tbl1],
U1.FullName AS [FullName from tbl1],
U2.idUser AS [idUser of Advisor],
U2.FullName AS [FullName of Advisor]
FROM USERS U1
INNER JOIN USERS U2 on U2.idUser = U1.idUser AND U2.Type = 'Advisor'

That said, there are some really cool things you can do with the VS designer - and it might be more suitable to your needs. In this case, you would probably need a query that pulls all the advisors and referrers back on a per-user basis.

Something like...

SELECT
U1.idUser AS [IdUser from tbl1],
U1.FullName AS [FullName from tbl1],
U2.idUser AS [idUser Of Referrer],
U2.FullName AS [FullName Of Referrer],
U3.idUser AS [idUser Of Advisor],
U3.FullName AS [FullName Of Advisor]
FROM USERS U1
INNER JOIN USERS U2 on U2.idUser = U1.idUser AND U2.Type = 'Referrer'
INNER JOIN USERS U3 on U3.idUser = U1.idUser AND U3.Type = 'Advisor'

edit: TYPO
edit2: left out a comma

tgabe213 · Oct 6, 2008

Are you manually populating the combo boxes or using the drag/drop designer method?

For design purposes right now, I manually entered that information into the USERS tables. In production, my add form will populate all of these fields (then the referral admin will populate other columns such as Memos, etc)

Edit: I'd like your take on what you think it is I'm trying to accomplish. I just want to make sure that we are on the same page with things.

What I'm trying to accomplish:
I work in the IT Dept for a bank with 38 locations. Everyday, our Investment Services 'Assistant' receives a lot of half sheets of paper filled out by Tellers/Customer Service Rep's in all of the branches. She puts this info into an Excel spreadsheet, blah blah blah. Some higher ups want this information to be readily available and accessible for viewing at any times. So the USERS in this case are our 2 different advisors (static. Does not change), our Referrers (tellers, csr's, maybe a loan officer), and our Customers. So then that GridView table will display the Customer's Name, the person they were referred by, their 'assigned' advisor, and any other relevant information. This data will be edited by the assistant, but I haven't gotten there yet.

tgabe213 · Oct 6, 2008

Why does "To get Referrers and To get Advisors" display each column twice? idUser, Name, idUser, Name.

Better yet, it's displaying the same userid and name in both sets of columns. Wouldn't I want 1 set to show the id/name of the referrer and the other set for the customer?

I am looking into your suggestion.

calebb · Oct 6, 2008

tgabe213 said:
Why does "To get Referrers and To get Advisors" display each column twice? idUser, Name, idUser, Name.

You left out the AND in the join?

tgabe213 · Oct 6, 2008

Nope. Copy/Paste

calebb · Oct 6, 2008

tgabe213 said:
Nope. Copy/Paste

Oh..columns

Take out some pieces from the SELECT statement. You are SELECTing 4 things so there will be 4 columns.

If you only want the referrer, just keep the selects from U2. But without the idUser from U1 (the customer), how will you connect the user to the referrer? The two places you can do this are in the WHERE idUser = 1234 or by selecting all idusers....

In .NET you can filter them out very easily:

DataRow[] arrayOfDataRows = dt.Select("idUser = '" intIdUser + "'"); //make sure you get your tick/quote nesting correct

Then you can bind your array of data rows or whatever...

calebb · Oct 6, 2008

tgabe213 said:
Edit: I'd like your take on what you think it is I'm trying to accomplish. I just want to make sure that we are on the same page with things.

You wouldn't learn nearly as much if I did that

By gnashing your teeth now, you'll become a very valuable programmer later

tgabe213 · Oct 6, 2008

calebb said:
You wouldn't learn nearly as much if I did that By gnashing your teeth now, you'll become a very valuable programmer later

Good point. I just wanted to make sure we have the same direction when talking about this project. Even if you thought it was something different before, you have my take on it now so it all makes sense.

tgabe213 · Oct 6, 2008

If you only want the referrer, just keep the selects from U2. But without the idUser from U1 (the customer), how will you connect the user to the referrer? The two places you can do this are in the WHERE idUser = 1234 or by selecting all idusers....

Yea, then that would be a simple SELECT * FROM USERS WHERE Type = "Customer", but that's not what I'm going for. I'm trying to break this down to understand selecting different types from the same table, then combining that with all of my other data to populate that entire table.

calebb · Oct 6, 2008

Try adding

WHERE U1.Type = 'Customer'

So the initial table you're doing the select from does not contain referrers or advisors.

tgabe213 · Oct 6, 2008

This shit is really aggravating when you have no idea what you are doing.

calebb · Oct 6, 2008

Maybe this will help!

What does a join look like? (I'll assume you're doing a SELECT * in all these cases)

Ok, you have this table:

USERS

Code:

idUser	FullName	Type
1	Joe		Customer
2	Jane		Referrer

Now, lets make two copies!

U1

Code:

idUser	FullName	Type
1	Joe		Customer
2	Jane		Referrer

U2

Code:

idUser	FullName	Type
1	Joe		Customer
2	Jane		Referrer

Now lets join them together on 1=1 - i.e., join every row to every row.
(or CROSS JOIN) i.e., SELECT * FROM USERS U1 CROSS JOIN USERS U2.

This is literally what the new table looks like.

U1 Joined to U2

Code:

idUser	FullName	Type		idUser	FullName	Type
1	Joe		Customer	1	Joe		Customer
1	Joe		Customer	2	Jane		Referrer
2	Jane		Referrer	1	Joe		Customer
2	Jane		Referrer	2	Jane		Referrer

It's every combination of every row in U1 with every row in U2.

Now, do you see the actual result set in there that you desire? Do you want only the rows where the left side Type is Customer and the right side Type is Referrer?

Perhaps

SELECT * FROM USERS U1 CROSS JOIN USERS U2 WHERE U1.Type = 'Customer' AND U2.Type = Customer

There's only one row above that matches this:

Code:

idUser	FullName	Type		idUser	FullName	Type
1	Joe		Customer	2	Jane		Referrer

p.s., I might have misled you on doing the inner join on U1.idUser = U2.idUser. This would have been the solution if you had kept the referrer information in the USERS_REFERRER table. However, since you moved Type to the USERS table, you are stuck doing an expensive CROSS JOIN.

tgabe213 · Oct 6, 2008

Thank you so much. For some reason, today I just cant wrap my head around anything. That makes 100% on what you did and why it works the way it does.

There never was a type in the referrer table. I guess there was a link to the referrer's in the users table, and I somehow must have over looked that til now.

What do you mean by expensive? Resources wise to process that join? I need to look a bit more into joins and what exactly they are comparing.

Edit:

....and now I have my advisor information too...

Code:

SELECT * 
FROM USERS U1 
CROSS JOIN USERS U2 
CROSS JOIN USERS U3
WHERE U1.Type = 'Customer' AND U2.Type = 'Referrer' AND U3.Type = 'Advisor'

Forgive me for being a bit incompetent in the field. I'm not even half way through my database class and we are just getting to data normalization (past modeling, just before sql).

calebb · Oct 6, 2008

tgabe213 said:
Thank you so much. For some reason, today I just cant wrap my head around anything. That makes 100% on what you did and why it works the way it does.

There never was a type in the referrer table. I guess there was a link to the referrer's in the users table, and I somehow must have over looked that til now.

What do you mean by expensive? Resources wise to process that join?

Yes. It has to do with the order that SQL queries are processed. The faster you can make your dataset smaller, the faster and less resource intensive your query will be.

FROM is the first keyword that is executed in a SQL statement - including the join. A cross join generates the largest dataset possible...

In you case it might not matter, but the scalability of this design will not be as scalable as one with a lookup table like the USERS_REFERRER table, or maybe a more generica USERS_RELATED table:

USERS_RELATED

Code:

idUser	idRelatedUser	RelationshipType
1	2		REFERRER

Code:

SELECT * FROM USERS U1
INNER JOIN USERS_RELATED UR --ALIAS USERS_RELATED TO UR
ON U1.idUser = U2.idUser --Immediately pare down to only U1.idUser relationships
AND U2.RelationshipType = 'REFERRER' --Further whittle down to only Referrers.

Or, without the comments:

Code:

SELECT * FROM USERS U1
INNER JOIN USERS_RELATED UR ON U1.idUser = U2.idUser AND U2.RelationshipType = 'REFERRER'

But... there I go again trying to tell you how to do things

Your method with the CROSS JOIN will work and get the results you need - but remember that your dataset will be "Number of Rows Squared" - i.e., if you have 10 rows in USERS, that cross join will generate a dataset with 100 rows.

If you bumped up to 100 rows, now your cross join generates a dataset with 10,000 rows that needs to be parsed by SQL server...

So, if your target audience is 100-500 users/referrers/advisors, you're probably fine to keep it as-is!

If you want to scale up to 5000 users/referrers/advisors, it's time to rethink it. (i.e., something a little closer to the visio I drew up for you)

calebb · Oct 6, 2008

tgabe213 said:
Thank you so much. For some reason, today I just cant wrap my head around anything. That makes 100% on what you did and why it works the way it does.

There never was a type in the referrer table. I guess there was a link to the referrer's in the users table, and I somehow must have over looked that til now.

What do you mean by expensive? Resources wise to process that join? I need to look a bit more into joins and what exactly they are comparing.

Edit:

....and now I have my advisor information too...

Code:

SELECT * FROM USERS U1 CROSS JOIN USERS U2 CROSS JOIN USERS U3 WHERE U1.Type = 'Customer' AND U2.Type = 'Referrer' AND U3.Type = 'Advisor'

Forgive me for being a bit incompetent in the field. I'm not even half way through my database class and we are just getting to data normalization (past modeling, just before sql).

HAWT! 2 cross joins. Now your dataset is # of rows cubed.

Now, with 100 Users/referrers/advisors, SQL server has to process:

100*100*100 = 1,000,000 rows.

Database table design and Keys

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

2[H]4U

2[H]4U

2[H]4U

2[H]4U

2[H]4U

2[H]4U

2[H]4U

2[H]4U

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

2[H]4U

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

2[H]4U

2[H]4U

[H]F Junkie

2[H]4U

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

[H]F Junkie

2[H]4U

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

2[H]4U

[H]F Junkie

[H]F Junkie