Advice on FK, please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Advice on FK, please

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-07-31 : 15:45:29

Say I've got two tables:

create table users (
  i int identity(1,1) PRIMARY KEY,
  name varchar(50)
  )

create table logins (
  i_users int,
  when smalldatetime,
  status tintint
  )

...However, the application in question allows for "guest" access, in which case the entry in logins has an i_users value of 0. There is no such record in the users table.

Now, should I create the relationship and just tell the server not to check data in createion or enforce it for inserts/updates? Is there any real point to that?

Alternatively, I could create a user with user ID 0 using identity_insert, I suppose, but that's ugly, because it isn't a real user, and I don't want someone to somehow login as that user (the app treats userID 0 as "guest", but I'd have to have add "and users.i<>0" all over the place for stored procedures that do lookups on users).

Thanks -- I know this is a fairly basic question, but foreign keys have definitely been one gap in my self-education.

Cheers
-b

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-31 : 15:58:32

Personally I'd create the user id of 0 to maintain the relationship. It preserves data integrity and the such.

You could reverse the relationship and make logins have the PK. This way you cxould have an entry in the logins table and not need one in users.

If thats not possible:

quote:
Alternatively, I could create a user with user ID 0 using identity_insert, I suppose, but that's ugly, because it isn't a real user, and I don't want someone to somehow login as that user (the app treats userID 0 as "guest", but I'd have to have add "and users.i<>0" all over the place for stored procedures that do lookups on users).

Theres always a run around. rename your users table to something like user. then create a view

create view users
as
select * from user
where i <> '0'

Everything now accesses the view instead. Heh, might not be the best way, but it's an option.

-----------------------
Take my advice, I dare ya

r937
Posting Yak Master

112 Posts

Posted - 2002-07-31 : 17:01:27

this is exactly why nulls were invented

use null as the guest fk, not 0

0 does not preserve data integrity, it corrupts it

when you want to join login and user information, just use

from logins left outer join users
     on i.users = i

in effect, "get all logins, and also any user info for logins that are known users"

rudy
http://rudy.ca/

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-07-31 : 18:19:10

Aiken,

I think if you ask yourself some basic questions about what a user is you will come up with an answer.

eg.
Q) What do you call someone who accesses the Application?
A) A User

.: Any person who accesses the system will be considered a user.

If you think this is a sound outcome then, a "guest" IS a user and should have a entry in the table to reflect it.

The next part relates to security of you application and the "guests" rights. M.E. has suggested a VIEW mechanism and this IMHO is the right choice.

I have to strongly disagree with rudy's options of a null value.
At least 0 is a value and not some busted arse "invention of nothing".

Here is an example of the predicate formed when using null and 0 for the logins

The 0 Option
"The User 0 logged into the system on 01/01/2001 and has a status of 1"

The null Option
"Nothing logged into the system on 01/01/2001 and has a status of 1"

Which of the above makes sense to you?

DavidM

"SQL-3 is an abomination.."

r937
Posting Yak Master

112 Posts

Posted - 2002-07-31 : 19:05:05

quote:
At least 0 is a value and not some busted arse "invention of nothing"

david, on the troll-o-meter ([url]http://www.tuxedo.org/~esr/jargon/html/entry/Troll-O-Meter.html[/url]) that'd be about a 7 or 8...

null means "don't know" as in "somebody logged in and we don't know who it is, it wasn't one of the users"

your "predicates" sound awful because you're not interpreting aiken's table design properly

but i'd have to agree with you on the sql-3 assessment

rudy
http://rudy.ca/

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-07-31 : 19:27:58

Actually I thought yours was troll.

null is "nothing", not anything, not a type, not a value, associated with nothing.

"it wasn't one of the users" because it has no meaning to anything let alone a user.

You can't even say "somebody logged in" because null isn't anything

Could you show me the correct predicates for the table design with the 2 options outlined?

DavidM

"SQL-3 is an abomination.."

r937
Posting Yak Master

112 Posts

Posted - 2002-07-31 : 20:56:16

quote:
Could you show me the correct predicates for the table design with the 2 options outlined?

i'm not sure what you mean by this, but i can compare the sql to retrieve information from those tables --

"0 option" means there's a user with i=0,
and guest logins have i_users=0

"null option" means only that
guest logins have i_users null

who are the users?

0 option:
select i, name
from users
where i <> 0

null option:
select i, name
from users

who logged in?

0 option:
select when, status, i, name
from logins inner join users
on i_users = i

null option:
select when, status, i, name
from logins left outer join users
on i_users = i

is that what you mean?

rudy
http://rudy.ca/

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-01 : 13:12:57

Well, this is definitely an interesting discussion, but I'm not sure I'm any closer to deciding what to do.

My problem with creating a user record for "guest", regardless of whether its user ID ("i") is 0 or NULL, is that it creates the need for all sorts of special cases throughout the application.

- Users log in as guest without entering a username or password; those links would have to pass the guest username and password to the normal login routine, or at least "guest" as the username, and the normal login routine would have to be modified to add the guest password.

- We detect compromised accounts by the number of logins from different IP addresses in a given time span. That would need the "and users.i<>0" (or "is not null").

...maybe the answer is to split the logins table to logins and logins_guest, since there is different data available. Then for reporting and such, a view could union the two?

Thanks for the discussion (and debate)...

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources