Database table design 1

[gazwalker]

Im trying to design a database to use in SQL server , the design i've got now is working but It just doesn't look right to me and there must be a better way to set things out. I've also noticed that potentially i've got a many to many relationship here

Heres the current situation

Theres 3 tables

USERTYPES

USERS

TICKETS

The users table holds the user information i.e.

userID
userName
Phone Number
userTypeID *

It contains a foreign key called userTypeID which is a primary key of USERTYPES

The USERTYPES table contains the two types of users on the system , these are standard and admin, so the table looks like this.


USERTYPESID
typeName

The TICKETS table holds the ticket details of tickets submitted by users. so

ticketID
ticketName
ticketDescription
DueDate
ticketRequestor *
AssignedTo *

Now here is where I have the problem, the ticketRequestor is a foreign key of the STAFF table staffID however AssignedTo is also a foreign key of the STAFF table staffID.

Currently in order to get this working i've had to turn off the enforce relationship for the AssignedTO - staffID link but idealy I think this enforce relationship should be on to ensure its a truely relational.

SOOO a ticket contains a staff member who opens the ticket and the ticket also contains another staff member who is class as an admin assigned to it. so each ticket can have 1 or more staff members assigned and each staff member cna have many tickets so there I have a many to many.

I'm not sure how to sort this , I could insert another link table say called

adminTicketLinkTable
--------------------
ticketID
staffID
--------------------

but I still dont think that looks right

any ideas as to what I need to do to organise the relationships better ?

thanks

 

[gazwalker]

just a little update / addition

would I be better creating a seperate table so insted of having the staff table and using the userRole table to determin if the users a standard user or a technician.

for example would I be better having a staff table and this would just hold the standard staff details then have a seperate technician table that just holds the technician details ?

 

[r937]

i got a little confused between "user", "staff", "standard", "admin", and "technician"

i'm going to assume these are all types of USERS

your initial design seems fine

TICKETS.ticketRequestor and TICKETS.AssignedTo can both be foreign keys to the USERS table

each is a many-to-one relationship

i guess in a way that's a many-to-many relationship, but you don't really need to go to a separate table to do this, as long as each ticket can be requested by only one user, and each ticket can be assigned to only one user



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[gazwalker]

ok but drawing out the tables a relation ships on paper that would mean there would be 2 one to many links between the STAFF and TICKET tables. Is it correct database design to have 2 links like this between the two tables ?

 

[r937]

yes, there is no problem whatsoever in having two columns in one table each with a foreign key relationship to the same other table

after all, they have completely different roles

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[gazwalker]

i've created the two different database diagrams in visio here they are

http://www.agentwalker.plus.com/database/version1.jpg

http://www.agentwalker.plus.com/database/version2.jpg

version1 is the version where I have seperated the staff table so theres now a technician table also.

Version2 is the current version where theres just the staff table and theres 2 links between staff and ticketDetails.
1 link for staff.staffID and ticketDetails.staffID and another link for staff.staffID and ticketDetails.assignedTo

hope this clears up what i'm trying to achieve, I;ve had a look in some of my old database books and i cannot see any examples where theres a double link between 2 tables like I have in version2 so thats why im not sure. Normally i would just accept that the current way works and stick with it but seen as the actual structure of the database is going to be assesed I need to get this correct not just working

 

[gazwalker]

just a little not to add , would add it to the previous message but theres no edit function that I can see

In visio when I try and create the seccond one to many relationship for the staff.staffID and ticketDetials.assignedTo visio is wanting to place a ticketID FK in the staff table ?? is this correct should I have the ticketID in the staff table as a FK ? if so why ? thanks again mate

 

[r937]

in the 2nd version, ticketID should not be in Staff

instead, there should be 2 FKs from ticketDetail to Staff

why did you rename ticketRequestor in ticketDetail to StaffID?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[gazwalker]

Ok had another look at it a realised the relationship for the 2nd link was the wrong way round as the arrow end is the 1 end .

i've sorted that now if the version 2 picture and linked AssignedTo To staffID and its auto set assignedTo as a FK which I THINK is correct.

take a quick peek see what you think

http://www.agentwalker.plus.com/database/version2.jpg

"why did you rename ticketRequestor in ticketDetail to StaffID?"

staffID is what its called in the database that I constucted and initialy I did that to make the link more clear with the StaffID in the staff table, I changed the wording above As I felt ticketRequestor better described what that field represented.

 

[r937]

looks okay now



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[gazwalker]

thanks mate

 

[gazwalker]

ohh ohhh one last brain teaser for you, this time regarding the solutions / problems / ticketDetails tables.

Initialy I had the solutions table linked directly to the tickets table insted of the problems table. So the problems table was linked to ticketDetails and solutions was linked to ticketDetials also.

But I thought a ticket cannot exisit without a problem so theres a link there. A ticket can exist without a solution so no link . finaly a solution cannot exisit without a problem so a link there.

does this sound / look right or am I talking crap again ?

 

[r937]

i have no idea

what's a ticket? what's a problem? what's a solution?

these depend on your business rules

off the top of my head i would say a solution would need to be linked only to its originating problem, and possibly to other problems that it also happens to solve

i.e. some problems spawn other problems, and some problems are related to other problems, so a solution could actually be related to multiple problems

only you can decide how it actually works

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[gazwalker]

thanks , looks ok then might have to decide how i'm going to handle the knowledge base as to that happens with the solutions and problems table as like you said a solution could belong to many problems and a problem could have many solutions in theory.

thanks again