Posted by Erland Sommarskog on 07/29/07 21:19

pbd22 (dushkin@gmail.com) writes:
> Users Table
>
> a) change primary key from userID to emailAddress
> b) leave userID as unique and not null

Or put a UNIQUE constraint on emailAddress. It does not matter that much,
but since FKs will be to UserID I prefer that to be the PK.

> 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

I realise that the table is called LastLogin. Does this mean that there
is only one row per user? In such case I would rather have the columns
in Users.

But the normal would be to maintain a history, and have all the logins
a user has performed. In such case you cannot make UserID unique, because
he is only able to log in once. And login_date cannot be the PK, because
then two users can't login the same day.

The primary key should be composite (UserID, login_date). By the way,
despite the name, I assume that login_date also contains a time portion?
Else this will not fly well, as a user would only be able to login once
per day.

To the other questions, yes drop login_id and email_address. And UserID
should be an FK to Users.

> 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

Again, you need a composite primary key (UserID, photo_name) For b, d
and e the same applies as to LastLogin.

> 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

Same here: you need a composite primary key. Again, b, d and e apply.

> 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

Again, you need a composite primary key. Again, b, d and e apply.

> 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?

You cannot of course have two columns with the same name, but it's
perfectly normal to have two columns that are foreign keys to the
same table. You will have to find good names for your user IDs, so
that you easily can tell them apart. Maybe searching_user_id and
returned_user_id.

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

The standard recommendation when people ask for help with queries is
that they provide:

o CREATE TABLE statement(s) for their table(s). Preferably simplified
to illustrate the problem at hand.
o INSERT statement(s) with sample data.
o The desired result given the sample.
o A short description what you are trying to achieve.

This helps a lot to clarify what you are trying to. Also, having
table and data as INSERT statements makes it simple to develop a
tested solution.

When you posted the old definition of the tables, you included some
INSERT templates from Mgmt Studio, but that does not cut it. It has
to be real test data. If you hope that me or anyone else is going to
fill in that for you, you're taking a gamble. And after all, you know
the business, so you should also know what is good test data.

But maybe you will be able to write the procedure on your own, once
you have cleaned up the data model.


In any case, I'm off for a trip, so I will be away for ten days or so.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]