Markierung gelesenen Beiträge (Flags setzen)

[Stefan]

Hallo Forumbesucher :-)

Ich mach mir grade Gedanken, um eine Verwaltung von Dokumenten und Formularen. Es geht um eine Oberfläche, die wir unseren Kunden zur Verfügung stellen möchten. Darin liegen Prüfungsberichte und/oder Prüfungsveraussetzungen/-Bedingungen als DOC oder PDF Dateien.

Es gibt nun viele Kunden, die sich am System anmelden können. Das regelt schonmal, dass man weiß, WER ein Dokument ansieht / herunterlädt.

Was wir errichen wollen ist folgendes: Ein Dokument kann und wird einigen Kunden zur Ansicht vorgelegt. Nun möchten wir gerne den Kunden anzeigen, ob er sich die Dokumente schon angesehen hat. Da ein Dokument von mehreren Kunden (als Gruppenrecht) angesehen wird, kann ich nicht ein Flag für gesehen/ungesehen setzen. Irgendwie muss eine Verknüpfung her zwischen Dokument und Kunde...

Soviel zur Theorie.

Praxis:

Ansatz 1: Ich dachte, ich nehm mir einfach noch eine kleine Datenbanktabelle in der ich die Kunden mit den Dokumenten verknüpfe und als Eigenschaft noch eine Readtime mit hinzupacke. Nachteil: Diese Tabelle wird schon bald eine riesige Menge an Datensätzen enthalten, was die Sache denke ich mal sehr verlangsamen wird.

Ansatz 2: Ich überlege, eine neue Tabelle zu machen, in der ich für jeden Kunden einen Datensatz schreibe (Kunde, ReadFlags). ReadFlags wird ein textfeld. Jede Stelle des Textfeldes dient als Flag (1|0) für das Dokument, mit der ID der Stelle im Textfeld.
Beispiel: DokumentID=5, Dokument gelesen? => SELECT SUBSTRING(ReadFlags, 5, 1) ...;
Nachteil hier ist sicherlich, dass mir irgendwann die Länge des Textfeldes (ca. 65000) zum Verhängnis wird und ich dann auch eine schrecklich langsame Abfrage erhalten werde (vermutlich). Zudem hab ich keine Info mehr über die ReadTime des Kunden.

Meine Frage nun:

Gibt es für dieses Problem schon einen praxistauglichen Entwurf, der nicht in die Knie geht, wenn viele Datensätze vorliegen. Wünschenswert wäre das Datum, also die ReadTime, aber es muss nicht unbedingt enthalten sein.

Danke für Eure Vorschläge.

Viele Grüße
Stefan

Beide Ansätze

[Cheatah]

Hi,

Ansatz 1: Ich dachte, ich nehm mir einfach noch eine kleine Datenbanktabelle in der ich die Kunden mit den Dokumenten verknüpfe und als Eigenschaft noch eine Readtime mit hinzupacke. Nachteil: Diese Tabelle wird schon bald eine riesige Menge an Datensätzen enthalten, was die Sache denke ich mal sehr verlangsamen wird.

nein. Dafür wurden Indexe erfunden. Diese Lösung ist schulbuchmäßig.

Ansatz 2: [...]

Wuaaaah.

Nachteil hier ist sicherlich, dass mir irgendwann die Länge des Textfeldes (ca. 65000) zum Verhängnis wird und ich dann auch eine schrecklich langsame Abfrage erhalten werde (vermutlich). Zudem hab ich keine Info mehr über die ReadTime des Kunden.

Dies lässt sich nicht sinnvoll abfragen oder verwalten. Einige DBMSse kennen zwar Bitmap-Indexe, aber schön ist das ganz sicher nicht.

Gibt es für dieses Problem schon einen praxistauglichen Entwurf,

Kreuztabelle. Sehr eindeutige Sache.

Cheatah

[Stefan]

Hi,

Ansatz 1: Ich dachte, ich nehm mir einfach noch eine kleine Datenbanktabelle in der ich die Kunden mit den Dokumenten verknüpfe und als Eigenschaft noch eine Readtime mit hinzupacke. Nachteil: Diese Tabelle wird schon bald eine riesige Menge an Datensätzen enthalten, was die Sache denke ich mal sehr verlangsamen wird.

nein. Dafür wurden Indexe erfunden. Diese Lösung ist schulbuchmäßig.

Ja, deswegen wars auch der erste Ansatz ;)
Aber stell Dir das bei einem Forum vor, wo es ja auch User / Posts gibt, die gelesen markiert werden sollen. Ab 10000 Postings bei 5000 Usern hat die DB-Table dann schon 50000000 Datensätze mit Readtime. Das ist dann doch irgendwann langsam, oder nicht? ;)

Ansatz 2: [...]

Wuaaaah.

Nachteil hier ist sicherlich, dass mir irgendwann die Länge des Textfeldes (ca. 65000) zum Verhängnis wird und ich dann auch eine schrecklich langsame Abfrage erhalten werde (vermutlich). Zudem hab ich keine Info mehr über die ReadTime des Kunden.

Dies lässt sich nicht sinnvoll abfragen oder verwalten. Einige DBMSse kennen zwar Bitmap-Indexe, aber schön ist das ganz sicher nicht.

Das ist wohl richtig (dass es nicht sauber ist), aber abfragen kann man es ja doch recht simple (siehe Beispiel). Wie gesagt, es ist halt begrenzt und das stört mich. Und ich vermute Zugriffe auf Textfelder sind wesentlich langsamer ... :(

Gibt es für dieses Problem schon einen praxistauglichen Entwurf,

Kreuztabelle. Sehr eindeutige Sache.

Kreuztabelle. Ich muss dazusagen, ich nutze MySQL. Also nix mit Pivot-Funktion ;) - Aber eine "Kreuztabelle" in der Form mit 0|1 ist ja noch über ein IF realisierbar. Nur, ist eben genau das nicht sehr langsam?! Gehen wir von den 50000 Dokumenten und 1000 Usern aus.

Danke für die Anregungen, ich hoffe auf weitere :)

Gruss
Stefan

[Cheatah]

Hi,

Aber stell Dir das bei einem Forum vor, wo es ja auch User / Posts gibt, die gelesen markiert werden sollen. Ab 10000 Postings bei 5000 Usern hat die DB-Table dann schon 50000000 Datensätze mit Readtime. Das ist dann doch irgendwann langsam, oder nicht? ;)

bei so 'nem schnuckeligen kleinen Tabellchen? Wenn das schon langsam ist, dann hast Du etwas deutlich falsch gemacht ;-)

Dies lässt sich nicht sinnvoll abfragen oder verwalten. Einige DBMSse kennen zwar Bitmap-Indexe, aber schön ist das ganz sicher nicht.
Das ist wohl richtig (dass es nicht sauber ist), aber abfragen kann man es ja doch recht simple (siehe Beispiel).

Ich sagte nicht simpel, sondern sinnvoll. Die Abfrage wird auf einen Full Table Scan hinauslaufen - das einzige, was man mit allen erdenklichen Mitteln vermeiden sollte. (Naja, bei nur 'nem halben Dutzend Datensätzen wäre es wohl noch effizienter als ein Index ...)

Und ich vermute Zugriffe auf Textfelder sind wesentlich langsamer ... :(

Zugriffe, die nicht über einen Index gehen, sind langsamer.

Kreuztabelle. Sehr eindeutige Sache.
Kreuztabelle. Ich muss dazusagen, ich nutze MySQL.

"Kreuztabelle" ist ein anderer Ausdruck für "n:m-Beziehung", und das kann auch MySQL.

Also nix mit Pivot-Funktion ;)

Pivot?

Cheatah

[Stefan]

"Kreuztabelle" ist ein anderer Ausdruck für "n:m-Beziehung", und das kann auch MySQL.

Also nix mit Pivot-Funktion ;)

Pivot?

"Kreuztabelle" ist ein Ausdruck, den ich bisher nur in Zusammenhang mit Access gelesen habe. Dort gibt es eine Pivot Funktion, um diese zu erstellen. Ehrlich gesagt mag ich Access aber noch weniger als Nachts aufs Klo zu müssen ;) (gut, dass mir das noch erspart bleibt)

Also kann ich davon ausgehen, dass mein erster Ansatz mit unter das schnellste ist, was ich machen kann? Setz die Datenmenge hoch auf 1000000 User mit 50000000000 Dokumenten. Überspitzen wir, wann gibt es Einbußen?

Ich möchte eben nicht von Anfang an auf ein "schlechtes" System aufbauen. Jetzt in der Planung gilt es sich richtig Gedanken zu machen, das spart später sicherlich einiges an Zeit :)

Danke & viele Grüße
Stefan

[Cheatah]

Hi,

"Kreuztabelle" ist ein Ausdruck, den ich bisher nur in Zusammenhang mit Access gelesen habe.

ach so. Nein, der Begriff ist bei Datenbanken recht üblich; selbst bei solchen wie Access ;-)

Dort gibt es eine Pivot Funktion, um diese zu erstellen.

Man lernt nie aus ...

Ehrlich gesagt mag ich Access aber noch weniger als Nachts aufs Klo zu müssen ;) (gut, dass mir das noch erspart bleibt)

Gut, dass mir bisher beides erspart geblieben ist ;-) Access kenne ich nur vom Überdieschultergucken.

Also kann ich davon ausgehen, dass mein erster Ansatz mit unter das schnellste ist, was ich machen kann?

Der schnellste, sauberste, beste. Ein DBMS ist darauf ausgelegt, genau solche Dinge mit höchstmöglicher Effizienz zu leisten.

Setz die Datenmenge hoch auf 1000000 User mit 50000000000 Dokumenten. Überspitzen wir, wann gibt es Einbußen?

Es gibt genau dann Einbußen, wenn Dein DB-Layout nicht (mehr) den Anforderungen entspricht. Zum DB-Layout gehören Tabellen, Indexe, aber auch die SQL-Statements. Es ist möglich, mit 10^10 Datensätzen nicht messbar langsamer zu sein als mit 10^1 Datensätzen.

Ich möchte eben nicht von Anfang an auf ein "schlechtes" System aufbauen.

Dann bist Du mit einer Kreuztabelle exakt auf dem richtigen Weg.

Jetzt in der Planung gilt es sich richtig Gedanken zu machen, das spart später sicherlich einiges an Zeit :)

Absolut.

Cheatah

[Stefan]

Super! Danke Dir vielmals, dass wir drüber gesprochen haben :)
Nun geh ich mit ruhigem Gewissen an die Sache ran.

Ich erstell dann mein SQL Statement mal zuhause und poste es hier mal :O)

bis dann

Gruss Stefan

[Stefan]

Hi,

hier das SQL Statement, habs mal auf Basis eines Forums gemacht, dh. es gibt Boards, Threads und Posts. Dieses Query sollte eine Liste über alle in einem Thread befindlichen Posts zeigen. Die sortierung erfolgt über posts.posy und die einrückung über posts.posx (damit spart man rekursion):

SELECT
 posts.id,
 posts.posx,
 posts.posy,
 posts.topic,
 posts.time,
 users.username,
 IF(NOT NULL postviews.time, postviews.time, 0) as viewed
FROM
 posts
LEFT JOIN
 users
 ON posts.username=users.username
LEFT JOIN
 postviews
 ON posts.id=postviews.postid
 AND postviews.username='$username'
WHERE
 posts.boardid='$boardid'
 AND posts.threadid='$threadid'
ORDER BY
 posts.posy ASC,
 posts.time ASC;

Passt doch so, oder?
Sollte schneller laufen als wenn ich da etwas mache wie:

(IF(posts.postid<60, postviews.mask60 & POW(2,postid)=POW(2,postid), IF(posts.postid<120, postviews.mask120 & POW(2,postid-60)=POW(2,postid-60), postviews.mask180 & POW(2,postid-120)=POW(2,postid-120)))) as readed

Wobei hier in der postviews-table drei felder sind mit jeweils 60 Zeichen mit 1 oder 0 (True | False) und das mir sogar noch die max. anzahl an posts / thread auf 180 beschränkt.

Oder was denkt ihr?! :)

Gruss
Stefan

[Cheatah]

Hi,

Passt doch so, oder?

was sagt EXPLAIN SELECT ...?

Cheatah

[Stefan]

Hi,

Passt doch so, oder?

was sagt EXPLAIN SELECT ...?

Hab den SQL etwas angepasst, war ein Fehler drin:

EXPLAIN
SELECT
 posts.id,
 posts.posx,
 posts.posy,
 posts.topic,
 posts.time,
 users.username,
 IF(postviews.time IS NOT NULL, postviews.time, 0) as viewed
FROM
 posts
LEFT JOIN
 users
 ON posts.username=users.username
LEFT JOIN
 postviews
 ON posts.id=postviews.postid
 AND postviews.username='bechte'
WHERE
 posts.boardid=1
 AND posts.threadid=1
ORDER BY
 posts.posy ASC,
 posts.time ASC;

EXPLAIN sagt:

+-----------+--------+----------------------+--------+---------+--------+------+-------+
| table     | type   | possible_keys        | key    | key_len | ref    | rows | Extra |
+-----------+--------+----------------------+--------+---------+--------+------+-------+
| posts     | system | BOARDSORT,THERADSORT | [NULL] |  [NULL] | [NULL] |    1 |       |
| users     | system | PRIMARY              | [NULL] |  [NULL] | [NULL] |    1 |       |
| postviews | system | PRIMARY              | [NULL] |  [NULL] | [NULL] |    1 |       |
+-----------+--------+----------------------+--------+---------+--------+------+-------+

Gruss
Stefan

[Stefan]

Host: MySQLSERV01

Database: test

Table: 'posts'

CREATE TABLE posts (
  id int(11) NOT NULL auto_increment,
  boardid int(11) NOT NULL default '0',
  threadid int(11) NOT NULL default '0',
  posx int(4) unsigned NOT NULL default '0',
  posy int(4) unsigned NOT NULL default '0',
  topic varchar(100) NOT NULL default '',
  time datetime NOT NULL default '0000-00-00 00:00:00',
  username varchar(20) NOT NULL default '',
  PRIMARY KEY  (id),
  FULLTEXT KEY SECONDARY (username)
  KEY TOPICSORT (topic,posy),
  KEY BOARDSORT (boardid),
  KEY THERADSORT (threadid),
  KEY TIMESORT (time),
) TYPE=MyISAM;

Table: 'postviews'

CREATE TABLE postviews (
  postid int(4) NOT NULL default '0',
  username varchar(20) NOT NULL default '',
  time datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (postid,username)
) TYPE=MyISAM;

Table: 'users'

CREATE TABLE users (
  username varchar(20) NOT NULL default '',
  name varchar(100) NOT NULL default '',
  vorname varchar(100) NOT NULL default '',
  PRIMARY KEY  (username)
) TYPE=MyISAM;

[Stefan]

Hi,

stellen wir uns mal vor, wir möchten ein Forum bauen und dort die Themen in einer Baumstruktur darstellen. Um den Baum möglichst ohne Rekursion zu erstellen haben wir zwei Felder, posx und posy. posy gibt die vertikale Reihenfolge (von oben nach unten) an, und posx die Tiefe der Einrückung.

Wichtig bei der Baumdarstellung ist nun, dass wir wissen möchten, ob der Besucher (username) gewisse Beiträge schon gelesen hat oder nicht. Das soll möglichst schnell und effizient geschehen.

Ein sturrer, uneinsichtiger Kumpel von mir meinte, es sei das Beste eine zusätzliche Tabelle anzulegen und einen zusammengesetzen primären Schlüssel auf ThemenID und Username zu legen und dann drei Felder a 60 chars zu haben, die als Bitflags dienen. Somit kann ein Thema max. 180 Beiträge haben (eigentlich ausreichend) udn er kann über Bitberechnungen schnell abfragen, ob ein Benutzer ein Post gelesen hat oder nicht. Für mich sieht das aber sehr sehr umständlich und nahezu schwachsinnig aus *g*. Schauen wir es uns doch einmal selbst an:

SELECT
 forum_posts.postid,
 forum_posts.posx,
 forum_posts.posttopic,
 forum_posts.nickname,
 forum_posts.posttime,
 ( IF(forum_posts.postid<60,
      forum_postviews.mask60 & POW(2,postid)=POW(2,postid),
      IF(forum_posts.postid<120,
         forum_postviews.mask120 & POW(2,postid-60)=POW(2,postid-60),
         forum_postviews.mask180 & POW(2,postid-120)=POW(2,postid-120)
      )
   )
 ) as readed

FROM
 forum_postviews,
 forum_posts

WHERE
 forum_posts.boardid=1
 AND forum_posts.id=1
 AND forum_postviews.boardid=1
 AND forum_postviews.id=1
 AND forum_postviews.nickname='besucher'

ORDER BY
 forum_posts.posy ASC,
 forum_posts.posttime ASC;

Ich steuere eine Lösung an, bei der ich mich ebenfalls darauf berufe für die vertikale Sortierung eine Spalte posy und für die Einrückung eine Spalte posx zu wählen, allerdings möchte ich die Verwaltung der gelesenen Beiträge - in meinen Augen - verbessern:

Es soll auch wieder eine extra Tabelle geben. Dort soll der zusammengesetzte Primärschlüssel aber über die Felder ThemenID, PostID und Username liegen und zusätzlich soll ein Feld mit der Eigenschaft time hinzukommen, um zu sehen, wann der Besucher das letzte mal den Beitrag gelesen hat. Das ergibt dann in meinen Augen ein Performanceschub, da die Abfrage wesentliche einfacher gestaltet ist:

SELECT
 forum_posts.id,
 forum_posts.posx,
 forum_posts.topic,
 forum_posts.time,
 forum_posts.username,
 IF(forum_postviews.time IS NOT NULL, forum_postviews.time, 0) as readtime

FROM
 forum_posts

LEFT JOIN
 forum_users
 ON forum_posts.username=forum_users.username

LEFT JOIN
 forum_postviews
 ON forum_posts.id=forum_postviews.postid
 AND forum_postviews.username='besucher'

WHERE
 forum_posts.boardid=1
 AND forum_posts.threadid=1

ORDER BY
 forum_posts.posy ASC,
 forum_posts.time ASC;

Meine Frage, wer liegt denn nun richtig?! :o)
Oder, um von der Frage nach richtig / falsch wegzukommen, welcher Ansatz löst die Aufgabe mit dem effizienteren Ergebnis?! :o)

In meinen Augen ist die Bildung eines kathesischen Produkts, so wie er es bei seiner Lösung macht, nicht das richtige, da hier erstmal unnötig viele Datensätze gebildet werden, um dann die richtigen auszuwählen, wohingegen ich nur die Posts auswähle und von diesen ausgehend nur die Abfrage, die ich auch benötige. Ist das so richtig?

Danke für Kritik / Meinungen / Antworten.

Gruss
Stefan