Database Design Question

Question

Post reply

Database Design Question

callum.bundy

SSC Enthusiast

Points: 167
More actions
September 1, 2008 at 8:34 am

#192214

Hi All,
I have a quick question on how best to accomplish a design task I have.
Basically, I need to end up with a table or view that has 3 columns, Username, Name, Profile.
Currently I have Username and Name in a table in a SQL DB. Users are automatically added into it by Sharepoint each day.
Profile is going to be a column of new data that matches up with a Username.
The problem I am having is, how do I create a SQL table where it shows this infomation, but also allows someone to add a profile to someone.
I have a new table that has Username, Profile, as columns. I then created a view that linked the two Username columns together, with a join that allowed all usernames from my main User DB to show up, and then show the users profiles. This works only if I have also added them into the new table, because obviously if they are not in there, the username shows up in the view, but that row of data does not exist in the new table itself.
So I am looking for a way that my new table will update automatically, on a timed basis, or manually, or when someone tries to add a profile for a user that does not yet have a profile it automatically creates the row.
I am using MSSQL 2005 and Access 2007 to design the tables etc.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735655 More actions · Answer 1

If these need to always happen, why not add Profile to the first table, give it a default value of a space?

You could use a trigger to ensure a new row there, but I'm not a big fan of triggers.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Hi there,

Is the Username --> Profile relationship a one-to-many or a one-to-one?

Also is it possible for a Username to exist without a Profile and visa versa?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

callum.bundy SSC Enthusiast Points: 167 More actions · Answer 3

Hi Steve, Christopher, Thanks for the quick replies

Unfortunatly I cannot add anything to the first DB as it is a table that belongs to Sharepoint. I need to create this new table for Sharepoint to import profiles for its own use. I need the username field to be its lookup so it can match a profile to a username.

Username:Profile is 1:1

A username can exist without a profile but a profile belongs to a username

Thanks

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Hi again,

Ok seeing as though you have to have a new table, and I'm with Steve in that I trig avoid triggers where I can.

Then I would prob suggest that if you only have 1 point of Entry for Profiles, e.g a Stored Proc, then I would put the logic in there, to add the profile and the corresponding username to your new table.

hope this helps

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeffrey Williams SSC Guru Points: 90291 More actions · Answer 5

Maybe I am misreading this - but it sounds like all you need to do is change the join in the view from an inner join to a left outer join.

callum.bundy (9/1/2008)
I have a new table that has Username, Profile, as columns. I then created a view that linked the two Username columns together, with a join that allowed all usernames from my main User DB to show up, and then show the users profiles. This works only if I have also added them into the new table, because obviously if they are not in there, the username shows up in the view, but that row of data does not exist in the new table itself.

When a profile is added to a user - it will be visible in the view.

Example:

SELECT u.Username

,u.Name

,p.Profile

FROM UserTable u

LEFT JOIN Profiles p ON p.Username = u.Username;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

Jeffrey Williams (9/1/2008)
Maybe I am misreading this - but it sounds like all you need to do is change the join in the view from an inner join to a left outer join.
callum.bundy (9/1/2008)
I have a new table that has Username, Profile, as columns. I then created a view that linked the two Username columns together, with a join that allowed all usernames from my main User DB to show up, and then show the users profiles. This works only if I have also added them into the new table, because obviously if they are not in there, the username shows up in the view, but that row of data does not exist in the new table itself.
When a profile is added to a user - it will be visible in the view.
Example:
SELECT u.Username
,u.Name
,p.Profile
FROM UserTable u
LEFT JOIN Profiles p ON p.Username = u.Username;

Bingo.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

callum.bundy SSC Enthusiast Points: 167 More actions · Answer 7

Thank you all for such quick help

I will give the above a go.

Thanks