Database table design and Keys

tgabe213 · Oct 6, 2008

Oh jesus. Haha...

I just took a look at the spreadsheet with the existing information, and she has ~225 rows in the spreadsheet. I don't think it will really get over 250 (depending on how long they want to keep them in there). For now, to get this into production and usable by the bank, I think I'll continue with my poor design and performance method. Once it's out there, and I don't feel rushed to have a finished product, I will probably go your route.

edit:

Or I'll just go with your method from the start. It's been a 9 hour day. I've gotta get out of here

Thanks again

calebb · Oct 6, 2008

tgabe213 said:
Oh jesus. Haha...

I just took a look at the spreadsheet with the existing information, and she has ~225 rows in the spreadsheet. I don't think it will really get over 250 (depending on how long they want to keep them in there). For now, to get this into production and usable by the bank, I think I'll continue with my poor design and performance method. Once it's out there, and I don't feel rushed to have a finished product, I will probably go your route.

<slightly negative chiding removed>

keep up the learning / good work.

tgabe213 · Oct 6, 2008

It's a very minor thing to the bank, but a big thing to me since it's my first 'major' project. There will be a couple of people each day that will add a new referral to the database, and a few more viewing the table of existing referrals. If that makes sense.

calebb · Oct 6, 2008

tgabe213 said:
It's a very minor thing to the bank, but a big thing to me since it's my first 'major' project. There will be a couple of people each day that will add a new referral to the database, and a few more viewing the table of existing referrals. If that makes sense.

I was mostly just giving you a hard time

You've been working hard on this project the last couple weeks and it sounds like it'll be great!

tgabe213 · Oct 6, 2008

No problem. I was actually a little surprised by your comment, but it doesn't offend me or anything. It was a long day and today is only Monday.

tgabe213 · Oct 9, 2008

http://www.mytestmule.com/projects/10_9_DBDiagram.png

That's how my tables are setup. I believe there are no relationships shown because I do not have any FK constraints.

And success? Based on one of your queries in post #79, I was able to come up with this:

Code:

SELECT * FROM USERS U1
INNER JOIN USERS_RELATED UR
ON U1.idUser = UR.idUser

You had one thing wrong. You had U2 rather that UR in the last line, but even I found that!
So that gives me an output of

Code:

idUser      FullName                                           SpouseName                                         genDate                 idUser      idRelatedUser RelationshipType
----------- -------------------------------------------------- -------------------------------------------------- ----------------------- ----------- ------------- --------------------
1           Joe Customer                                       NULL                                               2008-10-03 00:00:00.000 1           1             Customer
2           Josh Referrer                                      NULL                                               2006-01-03 00:00:00.000 2           2             Referrer
4           Brian Advisor                                      NULL                                               2008-10-06 00:00:00.000 4           3             Advisor

(3 row(s) affected)

tgabe213 · Oct 9, 2008

Note: These queries are being tested in SQL Server Management Studio

Alright, this is the query I came up with based on the information that I've found in this thread. I'm trying to populate the CurrentReferrals table, but I keep getting a few errors. I think my errors have to do with my joins,because the errors say that it simply can't find some of the SELECT items.

Code:

SELECT 
	USERS.idUser,
	USERS.FullName,
	USERS_RELATED.*,
	USER_REFERRALS.*,
	USER_MEMOS.*,
	USER_LOCATION.*,
	LOCATION_LOOKUP.*

FROM
	USERS U1

INNER JOIN USERS_RELATED UR ON U1.idUser = UR.idUser
INNER JOIN USER_REFERRALS UF ON U1.idUser = UF.idUser
INNER JOIN USER_MEMOS UM ON U1.idUser = UM.idUser
INNER JOIN USER_LOCATION UL ON U1.idUser = UL.idUser
CROSS JOIN LOCATION_LOOKUP

ORDER BY
	LOCATION_LOOKUP.LOCATION

This is what I came up with using the query builder. I don't get any errors. I'm getting the table to display, but with headings only, and no data. I have some test data in all of the tables.

Code:

SELECT     USERS.idUser, USERS.FullName, USERS_RELATED.idUser AS Expr1, USERS_RELATED.idRelatedUser, USERS_RELATED.RelationshipType, 
                      USER_REFERRALS.idUserReferral, USER_REFERRALS.idUser AS Expr2, USER_REFERRALS.DateMetWith, USER_REFERRALS.idReferral, 
                      USER_REFERRALS.SaleMade, USER_REFERRALS.RevenueGenerated, USER_REFERRALS.ReferrerPaid, USER_REFERRALS.Qualified, 
                      USER_REFERRALS.genDate, USER_MEMOS.idUserMemo, USER_MEMOS.idUser AS Expr3, USER_MEMOS.Memo, USER_MEMOS.genDate AS Expr4, 
                      USER_LOCATION.idUserLocation, USER_LOCATION.LocationName, USER_LOCATION.idUser AS Expr5, USER_LOCATION.genDate AS Expr6, 
                      LOCATION_LOOKUP.LocNum, LOCATION_LOOKUP.LocName
FROM         USERS INNER JOIN
                      USERS_RELATED ON USERS.idUser = USERS_RELATED.idUser INNER JOIN
                      USER_REFERRALS ON USERS.idUser = USER_REFERRALS.idUser INNER JOIN
                      USER_MEMOS ON USERS.idUser = USER_MEMOS.idUser INNER JOIN
                      USER_LOCATION ON USERS.idUser = USER_LOCATION.idUser CROSS JOIN
                      LOCATION_LOOKUP

tgabe213 · Oct 9, 2008

I quick typed the data relating to output in excel just to visually lay it out to make sure I had it correct, and so you guys could look it over. Remember, there are NO FK constraints, just PK's.
http://www.mytestmule.com/projects/data.png

tgabe213 · Oct 9, 2008

After some playing around,

Code:

SELECT
	*

FROM
	USERS U

INNER JOIN USERS_RELATED UR ON U.idUser = UR.idUser
INNER JOIN USER_REFERRALS UF ON U.idUser = UF.idUser
INNER JOIN USER_MEMOS UM ON U.idUser = UM.idUser
INNER JOIN USER_LOCATION UL ON U.idUser = UL.idUser

gives me all of my column headings, but no data. If I take out all but the 1st join, I can get the data from USERS and USERS_RELATED table to show. Why is that any different?

tgabe213 · Oct 9, 2008

This is driving me crazy. I can get the headings to show in multiple queries, but I can't get the data to show.

calebb · Oct 9, 2008

That's because you are using INNER JOINs

You need LEFT OUTER JOINs if you want the user to show, even if there are no notes for that user.

SELECT
*

FROM
USERS U

LEFT OUTER USERS_RELATED UR ON U.idUser = UR.idUser
LEFT OUTER JOIN USER_REFERRALS UF ON U.idUser = UF.idUser
LEFT OUTER JOIN USER_MEMOS UM ON U.idUser = UM.idUser
LEFT OUTER JOIN USER_LOCATION UL ON U.idUser = UL.idUser

tgabe213 · Oct 9, 2008

Thanks a lot. I really need to look into the specific differences so it makes sense why one and not the other.

Of course you had replied probably within a minute of me leaving work, but curiosity got me so I had to VPN in just to check.

My new issue: This may be related more on the .NET side than the database.
I get every single item from each table, and that is a start. However, I purposely have 2 memos for Joe Customer, but I don't want all of his information to be displayed as 2 separate rows on my output table. Is there a way to get both those 'memos' to display in 1 part of the table?

calebb · Oct 10, 2008

tgabe213 said:
Thanks a lot. I really need to look into the specific differences so it makes sense why one and not the other.

Of course you had replied probably within a minute of me leaving work, but curiosity got me so I had to VPN in just to check.

My new issue: This may be related more on the .NET side than the database.
I get every single item from each table, and that is a start. However, I purposely have 2 memos for Joe Customer, but I don't want all of his information to be displayed as 2 separate rows on my output table. Is there a way to get both those 'memos' to display in 1 part of the table?

Inner joins vs Outer joins:

When you're selecting from Table 1 and you want to EXCLUDE rows from Table 1 that don't have a match in Table 2, you do an Inner join. (i.e., if you

Code:

SELECT * FROM users INNER JOIN notes ON users.idUser = notes.idUser

... and there are no notes for a user? you'll get no results for that user)

When you're selecting from Table 1 and you want to INCLUDE all results from table 1 regardless of whether or not there is a match from Table 2, you use a left outer join: (i.e., if you

Code:

SELECT * FROM users LEFT OUTER JOIN notes ON users.idUser = notes.idUser

... and there are no notes for that user? You get results, but now Table 2 fields are NULL where there isn't a match (but Table 1 fields are all there).

So your question is an interesting one:
"Is there a way to get both those 'memos' to display in 1 part of the table? "

One way to do this is to pivot the memos in your query so your table looks like this:

Code:

idUser | FullName | SpouseName | Memo1 | Memo2 | Memo3 | Memo4|
---------------------------------------------------------------

... but it's more difficult to parse through your memos.

Another option is to use .NET datatable searches:

DataRows[] arrayOfDataRows = dtUsers.Select("idUser = '" intIdUser "'");

Then you know that arrayOfDataRows[0] has all your user info.
And for memos:
foreach (Datarow dr in arrayOfDataRows)
{
console.writeline(dr["memo"].ToString());
}

Yet another way to do it is with two selects - select * from memos.

Or there's always the .NET data relationships... http://msdn.microsoft.com/en-us/library/bh8kx08z(VS.80).aspx

tgabe213 · Oct 14, 2008

Another option is to use .NET datatable searches:

DataRows[] arrayOfDataRows = dtUsers.Select("idUser = '" intIdUser "'");

Then you know that arrayOfDataRows[0] has all your user info.
And for memos:
foreach (Datarow dr in arrayOfDataRows)
{
console.writeline(dr["memo"].ToString());
}

Could you explain how this works in a little more detail? I've been tryin to look around. If you select a ton of data, does it just go through all of your data, and pull out what you want line by line?

I'm getting confused again, because when I get all my data to display for the gridview, I've basically got 3 different lines of data. I need some of each row to fill up 1 row in the gridview on my page. What happens when I have multiple of each thing in my tables?

calebb · Oct 17, 2008

tgabe213 said:
Could you explain how this works in a little more detail? I've been tryin to look around. If you select a ton of data, does it just go through all of your data, and pull out what you want line by line?

I'm getting confused again, because when I get all my data to display for the gridview, I've basically got 3 different lines of data. I need some of each row to fill up 1 row in the gridview on my page. What happens when I have multiple of each thing in my tables?

Code:

MEMOS

| idMemo | Memo        | idUser
---------------------------------
|      1 | Hi!         |      1 |
---------------------------------
|      2 | Hello!      |      1 |
---------------------------------
|      3 | Howdy!      |      1 |
---------------------------------
|      4 | Guten Tag   |      2 |
---------------------------------

Ok, let's say you fill up your datatable with

Code:

SELECT 
	M.idMemo,
	M.Memo,
	U.idUser
FROM MEMOS M
INNER JOIN USERS U ON U.idUser = M.idUser

Now you have 4 datarows which are members of the datatable. And you also have all the memos for all the users.

Code:

int idUser = 1;

DataRows[] arrayOfDataRows = dtUsers.Select("idUser = '" intIdUser "'");

foreach (Datarow dr in arrayOfDataRows)
{
	Console.WriteLine(dr["Memo"].ToString());
}

This would output:

Hi!
Hello!
Howdy!

Code:

[B]int idUser = 2;[/B]

DataRows[] arrayOfDataRows = dtUsers.Select("idUser = '" intIdUser "'");

foreach (Datarow dr in arrayOfDataRows)
{
	Console.WriteLine(dr["Memo"].ToString());
}

This would output

Guten Tag!

...and you don't have to use Console.Writeline!

You could do comboBoxMemos.Add(dr["Memo"].ToString(); for example. Or anything else you want to do.

Incidentally, even though you are getting a large dataset from the database (this can be improved with the SQL statement), this is actually a pretty memory-efficient way of doing this operation.

When you select out the array of DataRows, you are actually working with references to the DataRows in the master datatable, not new copies of the same data. So you couldn't, for example, take that arrayOfDataRows and add them to a new table unless you first did dt.Clone(); and then made an actual copy of the data in the DataRows by using the .ToArray() method of the DataRow class. (i.e., DataTable dt2 = dt.Clone(); ... foreach(DataRow dr in arrayOfDataRows) { dt2.Add(dr.ToArray()); } ) - this would actually make a second copy of each memo.

tgabe213 · Oct 17, 2008

This looks very promising! Thank you so much for explaining that. I haven't had a lot of time to work on this the past few days.

Everywhere i looked, I was confused by what I was seeing when trying to figure out the searching. This makes sense, and I think it will allow me to complete my project!

Database table design and Keys

tgabe213

2[H]4U

calebb

[H]F Junkie

tgabe213

2[H]4U

calebb

[H]F Junkie

tgabe213

2[H]4U

tgabe213

2[H]4U

tgabe213

2[H]4U

tgabe213

2[H]4U

tgabe213

2[H]4U

tgabe213

2[H]4U

calebb

[H]F Junkie

tgabe213

2[H]4U

calebb

[H]F Junkie

tgabe213

2[H]4U

calebb

[H]F Junkie

tgabe213

2[H]4U