[dev] DB-table links

[Alexander Scholler]

Hi,

I have a problem in understanding the DB-schema illustrated in
ftp://ftp.otrs.org/pub/otrs/misc/otrs-2.0-database.png

Some links between tables presented in this PNG are

ticket.id to
  * article.ticket_id (1:n)
  * time_accounting.ticket_id (1:n)
  * ticket_history.ticket_id (1:n)
    -> attention: there's a mistake in the png!

article.id to
  * ticket_history.article_id
  * time_accounting.article_id

A link is present from article.ticket_id to time_accounting.ticket_id - 
these values must always be the same on linked table-entries.
The problem is that

select a.* from article a inner join time_accounting ta on a.id = 
ta.article_id where a.ticket_id != ta.ticket_id order by a.id;

return 27 results.

select a.* from article a inner join ticket_history th on a.id = 
th.article_id where a.ticket_id != th.ticket_id order by a.id;

return 91 results.

My DB-data is:
* ticket = 960 rows
* article = 3047 rows
* time_accounting = 2277 rows
* ticket_history = 21556 rows

There seems to be inconsistent date, because both queries shouldn't 
return any results != 0. I didn't manipulate the OTRS-DB manually, just 
used a brand new OTRS-installation v2.0.4 with no data stored in it.

What can be wrong?
Can this inconsistent be repaired? I don't think so!

The problem is that I get slightly different statistic results on my 
analysis of the data.

Bye, Alex





-- 
Alexander Scholler <alexander.scholler at augsburg.de>
Informationstechnik | Team Beratung und Betreuung
VZA / Zimmer 472, Durchwahl -4451