[DB-SIG] Portable method of determining which column wasn't u nique?

[Dustin Sallings]

Around 15:08 on Oct 10, 2002, Keating, Tim said:

# A better way to deal with this might be to break up your tables along
# functional lines. Right now it sounds like you are trying to store login
# credentials and user information in the same table. A safer way to do
# this might be (in pseudo-DDL):

	I don't think this line is completely clear.  You'll find many
places where username, credentials, and other user information are all
contained.  LDAP, old-school UNIX auth, NetInfo, and many apps that I've
written.  If you consider the table user-specific information, it makes
sense to keep it all together.  If a user will always have one set of
credentials and one set of information (name, etc...), then there's no
point of having two tables where a row in one guarantees both a row in the
other and a row in a mapping table hooking the two together.

	There certainly are cases where it makes sense to separate such
things, but I don't think they're very common.

# Table LoginCredentials (
# 	int login_id PRIMARY KEY,
# 	char[] username UNIQUE,
# 	char[] password_hash
# )
#
# Table UserInfo (
# 	int user_id PRIMARY KEY,
# 	char[] name,
# 	char[] emailaddress
# 	.
# 	.
# 	.
# )
#
# Table LoginToUser (
# 	int login_id,
# 	int user_id
# )
#
# Now, you can add rows for both username=username AND username=email address
# to the LoginCredentials table, use a much simpler query to log them in, and
# then retrieve their personal info through the LoginCredentials ->
# LoginToUser -> UserInfo join.
#
# This isn't perfectly normalized, since email address is stored twice, but
# since you are overloading the purpose of email address anyway, it may be a
# better model for you. It also solves the original problem, since there is
# only one unique column.
#
# TK
#
# -----Original Message-----
# From: Johannes Erdfelt [mailto:johannes@erdfelt.com]
# Sent: Thursday, October 10, 2002 2:58 PM
# To: db-sig@python.org
# Subject: Re: [DB-SIG] Portable method of determining which column wasn't
# unique?
#
#
# On Thu, Oct 10, 2002, Bob Gailer <ramrom@earthling.net> wrote:
# > At 02:52 PM 10/10/2002 -0400, you wrote:
# >
# > > I have a table which has multiple unique columns on it. My problem
# > > is that
# > > when I insert a row which violates the unique restraint, I can't figure
# out
# > > which column caused the problem.
# >
# > Just a stab in the dark - but I am suspicious of a data model that
# > requires
# > unique values in two (or more) columns. Can you verify that this is really
#
# > needed?.
#
# Yes. The two values are a username and an email address.
#
# username is unique for obvious reasons and the email address is unique
# because it provides an alternate means of logging in.
#
# > Also are the users of the system providing these values directly?
#
# Yes. The user chooses their own username and their email addres is their
# own.
#
# > You could :
# >    select * from table where unique-column1 = proposed-new-value1
# >    select * from table where unique-column2 = proposed-new-value2 If
# > you get a row in either case then you know the corresponding new
# > value's already in use,
#
# That has the same races as the possible solution I came up with that I
# described at the end of my original email.
#
# Another insert came come in between the select and the insert.
#
# I was hoping to make this efficient and not require explicitly locking the
# table since the table receives queries often.
#
# I was also hoping to keep it portable across multiple databases (hence the
# relevance to db-sig)
#
# JE
#
#
# _______________________________________________
# DB-SIG maillist  -  DB-SIG@python.org
# http://mail.python.org/mailman/listinfo/db-sig
#
# _______________________________________________
# DB-SIG maillist  -  DB-SIG@python.org
# http://mail.python.org/mailman/listinfo/db-sig
#
#

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________