3 fks extrct frm 1 tbl into one row on diff tbl

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

General SQL Server Forums

New to SQL Server Programming

3 fks extrct frm 1 tbl into one row on diff tbl

Author

Topic

becksalijin
Starting Member

2 Posts

Posted - 2007-11-20 : 14:25:57

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 15:03:40

Come again?

Or should that be: Why?

George
<3Engaged!

becksalijin
Starting Member

2 Posts

Posted - 2007-11-20 : 15:18:57

Hey guys,
m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible.
Is it possible to extract 3 foreign keys from a single table into one record/row on a different Table ?
i think i wrote it too fast and dint go through it i meant table and not
problem

the reason why is this
i need to create an EmployeeTbl which has three different types of employees who deal with one problem
there is one who calls in the problem
the 2nd one logs in the problem
and the 3rd solves the problem

Now i thought about making them into 3 different entities and showed it to my lecturer since this is for my assignment but i was told that isnt right and when i asked if it was possible to have 3 foreign keys from the same table onto one record/row she wasnt sure about and thats why i need some1 experienced abt this to tell me?

thanks again

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-20 : 17:04:11

Yes, it is possible, and if you are 100% sure that you will only have these three roles involved in a problem, and never have more than one person per role, then it is acceptable to create your table this way.
But if you may have different roles in the future, or you may have more than one person work on solving the problem, then you should really use a subtable to store the employees related to each problem.

e4 d5 xd5 Nf6

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-20 : 18:47:35

Hi there becksalijin,

I'm no pro when it comes to SQL, but here is what I just put together and tested for you and it seems to work fine.

CREATE TABLE tblProblems(
	ProblemID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	CallerID INT NOT NULL REFERENCES tblEmployees(CustomerID),
	LoggerID INT NOT NULL REFERENCES tblEmployees(CustomerID),
	SolverID INT NOT NULL REFERENCES tblEmployees(CustomerID),
        ProblemDesc VARCHAR(100) NOT NULL,
        .
        .
        .
);

If there is something wrong with it then hopefully one of the pros will point us in the right direction.

Butterfly82

PS maybe you want to put WHERE clauses somewhere in there to determine what type of employee can go in each of the three corresponding fields.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-20 : 23:37:02

quote:
Originally posted by Butterfly82

Hi there becksalijin,

I'm no pro when it comes to SQL, but here is what I just put together and tested for you and it seems to work fine.

CREATE TABLE tblProblems(
ProblemID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CallerID INT NOT NULL REFERENCES tblEmployees(CustomerID),
LoggerID INT NOT NULL REFERENCES tblEmployees(CustomerID),
SolverID INT NOT NULL REFERENCES tblEmployees(CustomerID),
ProblemDesc VARCHAR(100) NOT NULL,
.
.
.
);

If there is something wrong with it then hopefully one of the pros will point us in the right direction.

Butterfly82

PS maybe you want to put WHERE clauses somewhere in there to determine what type of employee can go in each of the three corresponding fields.

pronounYou verbForgot prepositionTo verbPut "col" prepositionIn notsureFront prepositionOf articleThe nounColumn nounNames.

elsasoft.org

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-21 : 11:40:59

Thanks for that Jezemine (nice name BTW, I like it)

I've just been looking up naming conventions since reading your post and the conclusion I came up with is that because I used the 'tbl' prefix for the table that the way to go then is to use 'col' as a prefix for all column names. Do I understand where you’re coming from?

I didn't really take into account the column names when I wrote the table, but looking back I think I would name the FK fields differently too as these clash with the tblProblems PK. What would you suggest using for them?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-21 : 22:23:08

sorry, no, i didn't mean that. quite the opposite. was trying to be clever, looks like I failed.

i have always hated hungarian notation. to me it makes things hard to read and is simply ugly.

however it does have its uses. see item #30 here: http://mindprod.com/jgloss/unmainnaming.html

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources