Posted by pbd22 on 07/29/07 19:18

Thanks a ton Erland.

In response to your questions:

> Please answer these question:
> 1) Can there be a user for which there is no email address registered?
ANSWER: NO
> 2) Can there be two users with the same email address?
ANSWER: NO

> So my assumption is that the answer is no both questions,
> and that emailAddress should be the primary key of this table... [SNIP]

OK. I have summarized the changes that you have suggested below, let
me know if you agree with them:

Users Table

a) change primary key from userID to emailAddress
b) leave userID as unique and not null

Last Login

a) create userID column, set it as not null and unique
b) remove primary key from login_id column (should I delete this
column?)
c) make login_date the primary key column
d) set foreign key from userID in Last Login (souce) to userID in
Users (destination)
e) delete the email_address column

PersonalPhotos

a) create userID column, set it as not null and unique
b) remove primary key from photoId column (should I delete this
column?)
c) make photo_name the primary key column
d) set foreign key from userID in PersonalPhotos (souce) to userID in
Users (destination)
e) delete the user_name column


SavedSearches

a) create userID column, set it as not null and unique
b) remove primary key from search_id column (should I delete this
column?)
c) make search_name the primary key column
d) set foreign key from userID in SavedSearches (souce) to userID in
Users (destination)
e) delete the email_address column

EditProfile

a) create userID column, set it as not null and unique
b) remove primary key from edit_id column (should I delete this
column?)
c) make edit_date the primary key column
d) set foreign key from userID in EditProfile (souce) to userID in
Users (destination)
e) delete the email_address column


UserPrecedence

I figure I should bring up UserPrecedence at this point.
UserPrecedence is a table that is used to describe the results of a
ZipCode search. The order of the results is
detailed in the UserPrecedence table. I had this hard-coded before but
realized that if a user deletes himself from the system, that will
cause errors in the saved results. By creating the UserPrecedence
table, I am able to delete records here also when a user removes
himself.

So, the way I originally had it was:

email_address - is the user conducting the search
userID - is the userID of the user returned in the search results
up_order - is the ZipCode order (by distance) in the results
search_name - the name of the saved search

But, since we have been using a surrogate key, removing email_address
columns and replacing it with userID to denote the current user, I am
wondering if it is possible to have two userID columns (one describing
the user that conducted the search and the other the users in the
results)? I am guessing not. How would you handle this?

Finally, here is a sketch of the updated Data Model per your suggested
changes (its in UML form; asterisks to the left mean "not null"):

http://i103.photobucket.com/albums/m156/pbd22/SavedSearch_DataModel.jpg

Thanks again for your help.
Peter

PS - I thought I had explained what I am trying to do with this
procedure. But, maybe you are looking for a different response? This
procedure returns the results of user-customized searches. The top
part calls saved search terms from the SavedSearches table and stores
them (gender, country, etc) as local parameters.
Those parameters are then used to sort against the relevant JOINed
tables, encapsulating the results in the alias SavedSearch. The bottom
third of the stored procedure searches against the results in
SavedSearch, using the Row_Number function to return paging
information with the final table. The final result is a series of
profiles per the user's original saved search terms.

If I still am not providing what you want to hear, maybe you could
elaborate a little on what you expect in my explanation?

[Back to original message]