Implementing Specialisation

[breeb]

I have a table 'Problem' and each problem has a specific problem type. The problem types each have different attributes and so I have created a separate table for each one (there are 3:batch, online and system).

I have a the primary key for Problem (ProblemID) as a promary key for each of the tables and have created a one-to-one relationship for each one. This is incorrect as a problem can only be of one problem type which is not the case in the current structure.

I need help on how to set this relationship up in SQL
Server 2000. Any ideas?

 

[RiazKhanmohamed]

it's a one to many relationship - one problem can only be one type, but one problem type can be related to many problems.

you need a middle table, with problemid and problemtypeid, that relates the relevant table, to remove the one to many.

hope this helps

 

[breeb]

Thank you, that makes more sense.

I have created table ProblemType with the key being ProblemID and having ProblemTypeID as an attribute and created a 1-1 relationship with Problem. Then I have created a 1-many relationship with ProblemType and the other tables using ProblemTypeID which is the key for all the tables.

That sound right?

 

[RiazKhanmohamed]

yeah. actually i'm a bit dim.

the initial table was a one to many. that's fine. you only need to normalise many to many relationships. because it's one(problemtype) to many (problem), there's no need of a separate table. sorry, messed up a bit. sorry if i've caused you problems.

right. here goes my fix for you relationship

your problem table has problem id. that's fine. within this table you have a field type called problemtype. this is a foreign key.

you then have a table called problemtype. with problemtypeid (1-3). this is a primary key.

You then link this problemtypeid to problemtype field in problem. this is your relationship.

in sqls server you don't need to set up the links, you just connect them when you run sql statements using inner joins.

again, sorry for the mix-up.

 

[breeb]

No problem.

Not quite sure that is the solution or else I'm not understanding completely! How would the problem type table link up to the tables for the different problem types? For example, the Batch table has the attributes procedure and program which could be different for every problem.

 

[RiazKhanmohamed]

hang on, let me just get this straight. i think i've gone square.

you have the problem table that's fine.

you have a problem type table. surely for each problem type, you have one row? therefore it's a table with three rows?

or do you have multiple rows for each problem type?

 

[breeb]

I think that I'm confusing myself here!

I have a problem table and a table for each problem type ie. one for batch, one for online and one for system. A problem can only be of one problem type, and a problem type can be associated with many problems.

 

[RiazKhanmohamed]

right. now i get it. 3 tables. hmmmmmm.....are the three tables of the same type? i don't think you can link one key to three tables. if all the three tables have the same column names i would suggest putting them together. otherwise the best way would be to have three columns in your problem table, one relating to each individual table. doesn't really sound feasable to me.

either way you need a mid table that stores the id from the problem type tables (which would need to not overlap ever) and the problem id.

 

[JamesLean]

I think you're approaching this from the wrong angle. The only reason you have three different tables for the types is that they all have different columns/attributes?

You're not actually making a one-to-many relationship here, I think you're confusing yourself!

Sounds like you need to just have three simple tables BatchProblem, OnlineProblem, and SystemProblem. Then you insert each problem into the relevant table, depending on the type.

Make sense?

--James

 

[breeb]

Nope, the three tables are different, for each problem type there are different attributes that need to be stored so can't put them together. Don't think that the three columns in Problem would work either because that would,'t ure that each problem only has one problem type.

Think that I'll try the middle table again......

Thanks for your help!

 

[breeb]

Thanks James, don't think that would be the most efficient solution though as it would mean a lot of duplication of the problem attributes which is what I am trying to get away from.

I am sure that there is a very simple solution to this, just wish that I knew what it was!!!!

 

[JamesLean]

But you said that different problem types have vastly different attributes, which is why you're splitting them up in the first place?!

You may need to give us more detail about exactly what attributes each one has so we can provide better advice.

--James

 

[breeb]

Don't think that we're quite understanding each other!

What I have is a specialisation, Problem being the superclass and the different problem types the subclasses. There are a number of fields common to each problem and then each problem has a problem type and the attributes applicable to each problem type differ.

 

[RiazKhanmohamed]

are you going to have loads of entries in each table? if not then how about using id's that start at different numbers, like type 1 utilises id's 100 - 199, type 2 uses 200 - 299 etc then have the link table as such. not sure about your different type table layouts, but you should be able to link of that. would involve a lot of work linking back to the main table when you do come to run query's on the data though.

 

[JamesLean]

Possible table design:

Problem
ProblemID : PK
Type : Batch, Online or System
...

BatchProb
ProblemID: FK to Problem.ProblemID
...

OnlineProb
ProblemID: FK to Problem.ProblemID
...

SystemProb
ProblemID: FK to Problem.ProblemID
...

So you have the main problem table and then a row in only one of the other three "sub" tables, depending on the type.

To be honest, I don't think that's the easiest way of doing things, especially when it comes to querying the data, but it may work.

--James