Newbie Question on SQL DB

Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:

> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com