Table design question... - 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

General SQL Server Forums

Database Design and Application Architecture

Table design question...

Author

Topic

wkey1980
Starting Member

4 Posts

Posted - 2011-05-22 : 19:19:39

Hi All

I'm very new to SQL design and development and am really struggling to decide how to go about achieving what I need...

I am designing a knowledgebase with a SQL back end where a problem may relate to more than one symptom. My plan was to have one table for problems and one table for symptoms.

In the symptoms table I was planning to have a column called problemID that stored multiple problem IDs from the problems table.

Firstly, I have no idea whether this is the best way to achieve this one to many relationship and secondly, I don't know how to set this up. Any advice would be gratefully received.

Thanks!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-22 : 20:40:10

It would seem that a problem could have one or more symptoms. each problem sharing an exact copy of symptoms is unique. In which case you might want to have a symptom table that contains a list of all possible symptoms which can be used by a problem table. The problem table would be used to identify a problem that has one or more many symptoms.

Create table Symptoms
(
SymptomID int,
Symptom varchar(50)
)

Create table Problems
(
ProblemID int,
SymptomID int,
Problem varchar(50)
)

hope this works for you.

wkey1980
Starting Member

4 Posts

Posted - 2011-05-23 : 03:25:52

Thanks for your response.

Given your suggested table layout would I be correct in assuming that the SymptomID column in the Problems table would need to store multiple values?

If so, could you point me in the right direction for achieving this?

Really appreciate your helping me get this straightened out in my head.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 06:20:16

>> SymptomID column in the Problems table would need to store multiple values
No that should never happen (usually).

The problems table represents multiple links of a problem to symptoms by one row for each lnk.
It represents an m:n join and is called a conjoint table (by some people).

I think there shuld be another table though

Create table Symptoms
(
SymptomID int,
Symptom varchar(50)
)

Create table Problems_Symptoms
(
ProblemID int,
SymptomID int
)

Create table Problems
(
ProblemID int,
Problem varchar(50)
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wkey1980
Starting Member

4 Posts

Posted - 2011-05-23 : 07:19:35

Thanks Nigel

Seems to make much better sense to create the third table.

Really appreciate your help. I was struggling to picture how it should all hang together but that's made it much clearer.

wkey1980
Starting Member

4 Posts

Posted - 2011-05-23 : 11:54:11

Hit a point where I seem to be banging my head against the wall!

I've implemented the table design suggested by Nigel and am trying to right a query for the front end website that will display a problem based on the selection from a drop down box. The dropdownlist would display a list of values from the symptoms column.

So in my head the query should look something like this...

select * from problems
where Problems_Symptoms.SymptomID = dropdownlist selectedvalue

Does that make sense? Should I be able to return a problem based on the dd selecttion using this logic?

Thanks again!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 12:47:57

Are you selecting one syptom or a selection?

If it's just one then your query looks ok.
If it's more than one then you need to say whether it's any of them or all of them.

I would recommend putting this into a stored procedure as you will soon need to parse a list and that's a lot easier if you can split it up into multiple statements.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources